-1

enter image description hereI have column 'jobstarttimeiso' and I want to create another column for Weeks of the year based on the date. How would I go about doing that? I am using Redash to query from redshift database. Please help! Thank you.ash

Phillip Le
  • 17
  • 6
  • How do you define "Weeks of the year"? Do you mean a "Week Number", where the first week is Week 1 and the last week is Week 52 or 53? How do you define the "first week" — is it a full week, or does it start on a particular day (eg Sunday or Monday)? Take a look at the [DATE_PART Function - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html). It has a "week" capability. – John Rotenstein Apr 11 '20 at 02:48
  • Yeah I've tried date part and for some reason it doesn't work. Week 1 - 52 – Phillip Le Apr 12 '20 at 01:14
  • Please define "doesn't work". Did you receive an error? Or were you just unhappy with the result? How did the result differ from what you were seeking? – John Rotenstein Apr 12 '20 at 01:38
  • Sorry - I received an error. – Phillip Le Apr 12 '20 at 04:14
  • I added a picture above as reference when using date_part – Phillip Le Apr 12 '20 at 04:16
  • What is the field type of `jobstarttimeiso`? Is it a timestamp or a varchar? – John Rotenstein Apr 12 '20 at 05:35

1 Answers1

0

The extract function will return a week number e.g.

select extract(week from jobstarttimeiso)  as weeknumber

In general:

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp } )

See: https://docs.aws.amazon.com/redshift/latest/dg/r_EXTRACT_function.html

alternatively you can also use to_char()

TO_CHAR (timestamp_expression | numeric_expression , 'format')

with the parameter IYYY as the format for ISO 8601 week-numbering year (4 or more digits)

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51