Search code examples
dateamazon-redshiftweek-numberredash

How to create 'Week column' based on another date column?

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

Solution

  • 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)