1

I'm having some issues with the Presto syntax in grouping data by separate weeks, ending on Saturdays. I have this field called "timestamp," and I've been using the the following to group by weeks. However, I noticed that date_truncs weeks ending on a Monday and using a date_add doesn't necessary group all rows correctly.

select date_add('day', 5, date_trunc('week', date(substr(timestamp, 1, 10)))) as week_ending

Any suggestions on how I can group weeks ending on Saturdays? Thanks!

1
select substring(
          cast(date_add(
                  'day',
                  5+(cast(dow(
                             date_parse('2020-04-12','%Y-%m-%d')
                             )=7
                          as integer)
                    )*7,
                  date_trunc(
                     'week',
                     date_parse('2020-04-12','%Y-%m-%d')
                  )
               ) as varchar
          ),
          1,
          10
       ) as week_ending;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.