Getting day of week from date column in prestosql?

Question

I have a date column called day such as 2019/07/22 if I want to create a custom field that translates that date to the actual day of week it is such as Sunday or Monday how is this possible? I cant seem to find a method that works for presto sql.

Thanks for looking

Solution

You can use the format_datetime function to extract the day of week from a date or timestamp:

SELECT format_datetime(day, 'E')
FROM (
  VALUES DATE '2019-07-22'
) t(day)

produces:

 _col0
-------
 Mon

If you want the full name of the day, use format_datetime(day, 'EEEE'):

 _col0
-------
 Monday