Date_Part on SQL Athena - "Function date_part not registered"

I'm trying to find latest Sunday (just for the sake of the example)

In the link below I have found an explanation how to use a date function called date_part to extract the day of week (for example, for current timestamp) and other parts of the date which might be interesting.

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

The gist of it:

for example to find day of week

select date_part(dow, starttime) as dow from event

But when I try to run something similar on Athena I receive "function date_part not registered".

So how can I find the latest Sunday? Or any other day of the week for that matter.

Solution 1:

You can combine current_date with day_of_week to get the last Sunday:

presto:default> SELECT date_add('DAY', -day_of_week(current_date), current_date);
   _col0
------------
 2019-12-15
(1 row)

Note: when it's run on Sunday, this returns previous Sunday. You can easily adjust this as needed with if.

Tested on Presto 326.

As Athena is currently based on Presto .172, this is where you can learn about all available functions: https://trino.io/docs/0.172/functions.html

Solution 2:

AWS Athena uses Presto so you need to use the Presto date/time functions. (Redshift, on the other hand, seems to be loosely based on PostgreSQL.)

To get the latest Sunday, you should use day_of_week() to find Sundays, and you can restrict your query to dates in the last week to limit it to the most recent Sunday.