postgresql Postgres：从时间戳更新日期并保留时间

Postgres: Update date and retain time from timestamp

提问by Arnold Cristobal

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

我有一个 field1 timestamp，datatype值格式是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?

它将时间转换为全零。如何更改日期并保留时间戳？

回答by Giorgos Betsos

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

演示在这里

回答by Belayer

The subtraction of timestamps yields an interval. The resulting interval is can the be added to the desired date to give the desired date with the prior time.

时间戳的减法产生一个间隔。结果间隔可以添加到所需日期以提供具有先前时间的所需日期。

with ats (old_tz) as (select now() )  
select old_tz, '2015-12-31'::timestamptz + (old_tz - date_trunc('day', old_tz)) new_tz 
from ats;

OOPS. Didn't realize how old this post was until after posting, but still believe it may valuable to future viewers. So I'll just leave it.

哎呀。直到发布后才意识到这篇文章有多老，但仍然相信它对未来的观众可能很有价值。所以我会留下它。