postgresqltimestamp

Postgres: Update date and retain time from timestamp

I have a field1 with timestamp, datatype and values format is 2016-02-23 12:01:30.

I'm running the query:

UPDATE <table> set field1 = '2015-12-31'::timestamp::date where .....

The output changes to:

  2015-12-31 00:00:00

It converts the time to all zero's. How to change the date and retain the timestamp?

Solution

  • Try this:

    UPDATE mytable 
SET field1 = '2015-12-31'::timestamp + 
             EXTRACT(HOUR FROM field1) * INTERVAL '1 HOUR' +
             EXTRACT(MINUTE FROM field1) * INTERVAL '1 MINUTE' +
             EXTRACT(SECOND FROM field1) * INTERVAL '1 SECOND' 
WHERE ...

    Demo here