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

Asked 2011-05-26T02:32:22.430

433

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?

Edit Report

2 Answers

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

answered 2011-05-26T02:39:47.043

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)

answered 2011-05-26T02:40:34.250

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

2 Answers

Your Answer