0

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?

1

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.

  • 2
    The EXTRACT(DOW FROM x) is what the SQL standard requires. That "quirky syntax" is not Postgres specific. And '2011-07-07' is a string constant in SQL, not a DATE. The SQL standard for a DATE constant would be DATE '2011-07-07' - again nothing specific to Postgres. – a_horse_with_no_name Sep 10 '16 at 13:54
  • @a_horse_with_no_name I think the quirky syntax is referring to the :: casting but I agree. It's not obvious what is referring to so it can be misinterpreted (so it's probably better to remove it. – ypercubeᵀᴹ Oct 13 '16 at 8:39

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.