Problem
In PostgreSQL, I want to extract only the date portion of a timestamp.
I need it to be a DATE type in Postgresql so that I may insert it into another table that requires a DATE value.
For instance, if I had 2011/05/26 09:00:00, I’d want 2011/05/26 09:00:00.
I tried casting, but all I got is the year 2011:
timestamp:date
cast(timestamp as date)
I tried to char() and to date() together:
SELECT to_date(to_char(timestamp, 'YYYY/MM/DD'), 'YYYY/MM/DD')
FROM val3 WHERE id=1;
I attempted to turn it into 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
In PostgreSQL, what is the best approach to get a date (yyyy/mm/dd) from a timestamp?
Asked by keren
Solution #1
By prefixing your timestamp with::date, you can convert it to a date. In psql, this is a timestamp:
# select '2010-01-01 12:00:00'::timestamp;
timestamp
---------------------
2010-01-01 12:00:00
Now we’ll set a date for it:
wconrad=# select '2010-01-01 12:00:00'::timestamp::date;
date
------------
2010-01-01
You can, on the other hand, utilize the date trunc function. The distinction is that the latter delivers the same data type as timestamptz while maintaining your time zone (if you need it).
=> select date_trunc('day', now());
date_trunc
------------------------
2015-12-15 00:00:00+02
(1 row)
Answered by Wayne Conrad
Solution #2
Use the date function as follows:
select date(timestamp_field) from table
You can use the following to convert a character field representation to a date:
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:
Answered by James Allman
Solution #3
Have you tried using mydatetime>::date to cast it to a date?
Answered by leonbloy
Solution #4
In Python 2.7, this works for me.
select some_date::DATE from some_table;
Answered by thedarkgriffen
Solution #5
Simply use TO CHAR(timestamp column, ‘DD/MM/YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
Answered by Elmira Behzad
Post is based on https://stackoverflow.com/questions/6133107/extract-date-yyyy-mm-dd-from-a-timestamp-in-postgresql