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

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)

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:

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