Postgres:更新日期并保留时间戳中的时间

Question

我有一个带有timestamp的字段1，datatype和值格式是2016-02-23 12:01:30。

我正在运行查询：

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

输出更改为：

  2015-12-31 00:00:00

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

Answer 1

试试这个：

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 ...

在这里演示

Answer 2

还有另一种方法，使用 DateTime 类型。

因此，如果要将表的日期设置为今天，则可以使用以下命令：

UPDATE table SET column = current_date::date + column::time;

使用"2019-02-23"切换current_date，它也应该可以工作。

Answer 3

只需添加新日期和现有时间。在这里：

UPDATE mytable 
SET field1 = '2015-12-31'::date + field1::time with time zone
WHERE ...;

甚至更干净的切割：

UPDATE mytable 
SET field1 = field1 - field1::date + '2015-12-31'::date
WHERE ...;

Answer 4

时间戳的减去会产生一个间隔。由此产生的间隔是可以添加到所需的日期，以给出具有先前时间的所需日期。

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;

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