Search code examples
sqldatetimestampamazon-athenapresto

DATE_TRUNC Starting on Sunday for SQL presto (Athena)

For as simple as this question may sound, I have not been able to find a straight forward solution. I am using AWS Athena, which is a presto engine to my knowledge.

I have variable 'created_at' which is a TIMESTAMP. How can I get DATE_TRUNC('week', created_at) to start in SUNDAY instead of MONDAY ?

I've seen documentation concerning %V or %X as possible solutions, but not sure where in the function to introduce this, that is, if it is the proper solution.

Essentially I want to count sales by week starting on SUNDAY ending on SATURDAY , I will be grouping by weeK

GROUP BY DATE_TRUNC('week', created_at)
Solution

  • You could use them in date_format function: date_format(created_at, '%X%V') (and use the result to group), but unfortunately they are not supported by presto at the moment.

    As a workaround you can just add one day:

    select week(created_at + interval '1' day)