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

User · Answer

This works for me in python 2 7   select some date  DATE from some table

User · Answer

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

User · Answer

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

User · Answer

In postgres simply   TO CHAR timestamp column   DD MM YYYY   as submission date

User · Answer

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      gt  select date trunc  day   now            date trunc ------------------------  2015-12-15 00 00 00 02  1 row

User · Answer

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

User · Answer

Have you tried to cast it to a date  with  lt mydatetime gt   date

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

Examples related to sql

Examples related to postgresql

Examples related to casting

Examples related to timestamp