Postgresql – How to find the day of week of a date-time field in postgres

datetimepostgresql

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While 

SELECT EXTRACT(DOW FROM '2011-07-07')

works, the following query does not work and I can't figure it out: 

SELECT EXTRACT(DOW FROM TIMESTAMP date) as day
FROM purchases GROUP BY day;

It's like postgres won't let me find the day of the week for fields but only constants. How do I find the day of week for a field?

Best Answer

The whole issue revolves around CASTing and quirky PostgreSQL syntax.

CASTing strings as DATEs:

It's very simple - your first line of code:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07');
ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: SELECT EXTRACT(DOW FROM '2011-07-07');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
ntest=#

does not work. This is because, as far as PostreSQL is concerned, '2011-07-07' is just a string literal, not a DATE. This does work:

ntest=# SELECT EXTRACT(DOW FROM DATE '2011-07-07');
 date_part 
-----------
         4
(1 row)

Note the extra DATE that CASTs the string as such. You can also CAST it this way:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07'::DATE);
 date_part 
-----------
         4

This double colon (::) is very much a PostreSQL thing. It's really quite "obvious", once you grasp the PostgreSQL syntax (it had me stumped for a couple of minutes!)

A date field:

ntest=# \d orders;
       Table "public.orders"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 ldate       | date    | 
 customer_id | integer | 
 amount      | integer |

So, we have a date field - with records as below:

ntest=# SELECT * FROM orders;
   ldate    | customer_id | amount 
------------+-------------+--------
 2009-10-08 |           3 |   3000
 2009-10-08 |           3 |   1500
 2009-11-20 |           2 |   1560
 2008-05-20 |           4 |   2060
(4 rows)

So, to get the "day of week" from a date (note the two FROMs - doesn't work without the FROM between brackets):

ntest=# SELECT EXTRACT(DOW FROM ldate) FROM orders;
 date_part 
-----------
         4
         4
         5
         2
(4 rows)

Now, for a timestamp field:

ntest=# \d timestamptest;
           Table "public.timestamptest"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 x      | timestamp without time zone |

And:

ntest=# SELECT * FROM timestamptest;
             x              
----------------------------
 2016-04-18 22:52:30.020979
(1 row)

And DOW (again, note the two FROMs):

ntest=# SELECT EXTRACT(DOW FROM x) FROM timestamptest;
 date_part 
-----------
         1

Quirky syntax (the double colon also OK, if you want to be really explicit), but it works fine once you've mastered it.

Related Question