Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

Question

I want to extract just the date part from a timestamp in PostgreSQL.

I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value.

For example, if I have 2011/05/26 09:00:00, I want 2011/05/26

I tried casting, but I only get 2011:

timestamp:date
cast(timestamp as date)

I tried to_char() with to_date():

SELECT to_date(to_char(timestamp, 'YYYY/MM/DD'), 'YYYY/MM/DD') 
FROM val3 WHERE id=1;

I tried to make it a function:

CREATE OR REPLACE FUNCTION testing() RETURNS void AS '
DECLARE i_date DATE;
BEGIN
    SELECT to_date(to_char(val1, "YYYY/MM/DD"),"YYYY/MM/DD") 
      INTO i_date FROM exampTable WHERE id=1;
    INSERT INTO foo(testd) VALUES (i);
END

What is the best way to extract date (yyyy/mm/dd) from a timestamp in PostgreSQL?

Deauville

Answer 1

You can cast your timestamp to a date by suffixing it with ::date. Here, in psql, is a timestamp:

# select '2010-01-01 12:00:00'::timestamp;
      timestamp      
---------------------
 2010-01-01 12:00:00

Now we'll cast it to a date:

wconrad=# select '2010-01-01 12:00:00'::timestamp::date;
    date    
------------
 2010-01-01

On the other hand you can use date_trunc function. The difference between them is that the latter returns the same data type like timestamptz keeping your time zone intact (if you need it).

=> select date_trunc('day', now());
       date_trunc
------------------------
 2015-12-15 00:00:00+02
(1 row)

Dentistry

doesn't work, just tried "select '2010-01-01 12:00:00'::timestamp::date;" . it returns just year 2011. i had already tried date(timestamp) and (timestamp)::date but i only get the year part in return not the full date that i need. – Deauville

@kerenk, Now that's odd. Did you try it in psql? – Dentistry

@keren, psql is a command-line utility--you're not using it (but do consider it). When you execute the query in pgadmin3, look at the data output pane. You can resize the columns; the default column size is too short to show the entire date and shows only the year. Use your mouse to expand that column and you should see the whole thing. – Dentistry

omg you're right. i feel so stupid. thanks for pointing it out. – Deauville

One case I ran into where this doesn't work is in Squirrel. With this syntax, Squirrel will give you an input box to input parameter values for parameter ":date". – Gripsack

It is helpful.. I am also using pgsql right now.. It is hammering on my head. Let me try this solution... :) +1 in advance. – Analysand

Didn't work for me in Hibernate native queries. Solution (with the now() function): CAST(NOW() AS date) – Kiln

Beware of ::date as it can get in conflict with parameters (:myParameter) in siome cases. – Rieger

It works fine, i got a small error, but i was using a column alias like this, column as c1::timestamp::date , then i change it to column::timestamp::date as c1 and it worked fine. – Lauer

Answer 2

Use the date function:

select date(timestamp_field) from table

From a character field representation to a date you can use:

select date(substring('2011/05/26 09:00:00' from 1 for 10));

Test code:

create table test_table (timestamp_field timestamp);
insert into test_table (timestamp_field) values(current_timestamp);
select timestamp_field, date(timestamp_field) from test_table;

Test result:

Pga

i've tried that but i only get 2011 in return instead of the full date like 2011/05/26 – Deauville

I realized you are not working with a timetamp data type. Revised to work with a string representation of a timestamp in the format you provided. – Pga

How are you executing the sql? psql? – Pga

i'm using pgAdmin III postgresSQL – Deauville

I noticed when I perform a test in pgAdmin III the 'date' column is only wide enough to display the year. Grab the column handle and expand the column to see the full date. – Pga

Answer 3

In postgres simply :

TO_CHAR(timestamp_column, 'DD/MM/YYYY') as submission_date

Swimming

Answer 4

Have you tried to cast it to a date, with <mydatetime>::date ?

Shown

This works just fine. As noted in the comments on Wayne Conrad's answer, keren was led astray by an excessively narrow column in pgAdmin's output pane. – Outgroup

Answer 5

This works for me in python 2.7

 select some_date::DATE from some_table;

Lazulite

Answer 6

Just do select date(timestamp_column) and you would get the only the date part. Sometimes doing select timestamp_column::date may return date 00:00:00 where it doesn't remove the 00:00:00 part. But I have seen date(timestamp_column) to work perfectly in all the cases. Hope this helps.

Puttier

Answer 7

CREATE TABLE sometable (t TIMESTAMP, d DATE);
INSERT INTO sometable SELECT '2011/05/26 09:00:00';
UPDATE sometable SET d = t; -- OK
-- UPDATE sometable SET d = t::date; OK
-- UPDATE sometable SET d = CAST (t AS date); OK
-- UPDATE sometable SET d = date(t); OK
SELECT * FROM sometable ;
          t          |     d      
---------------------+------------
 2011-05-26 09:00:00 | 2011-05-26
(1 row)

Another test kit:

SELECT pg_catalog.date(t) FROM sometable;
    date    
------------
 2011-05-26
(1 row)

SHOW datestyle ;
 DateStyle 
-----------
 ISO, MDY
(1 row)

Suzetta

i just tried yours in pgAdmin but d only has 2011 not full date which i needed! :( – Deauville

@keren: what about SELECT pg_catalog.date('2011/05/26 09:00:00'); ? – Suzetta

maybe it has something to do with formatting the output value. I'm thinking that the return value probably has day and month in there , but its just not shown on scree? – Deauville

typing in SHOW datestyle ; game me "ISO, DMY" – Deauville

Answer 8

You can use date_trunc('day', field).

select date_trunc('day', data_gps) as date_description from some_table;

Centeno