Week Numbers (1-53)

Today the extract() function does not support extracting the ‘week number’ from a date field. As a workaround you can use a formula like this (may need some adjustments +/-1 for certain years, could add some ifElse logic to account for that). FYI this uses weeks starting Sun.

dateDiff(truncDate("WK", truncDate('YYYY', {order_date})), {order_date}, 'WK')+1

2 Likes

Hi Jesse, thanks for providing this solution! Is there a way to get the week to start on Monday? I have had some trouble in the past with trying to align Quicksight with data from Athena, which does not have support for weeks starting on Sunday.

Presto documentation
%V Week (0153), where Sunday is the first day of the week; used with %X
The following specifiers are not currently supported: %D %U %u %V %w %X
https://prestodb.io/docs/current/functions/datetime.html

Nice, that helped a lot!

For the week starting on Monday:

ifelse(
    extract("WD", Date) = 1,
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK'),
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK')+1
)

This can be changed to any day of the week can be used as starting point.