在PostgreSQL中使用UTC当前时间作为默认值

我有一个列的时间戳没有时区类型，并希望有默认的当前UTC时间。获取当前UTC时间很简单:

postgres=# select now() at time zone 'utc';
          timezone          
----------------------------
 2013-05-17 12:52:51.337466
(1 row)

为列使用当前时间戳:

postgres=# create temporary table test(id int, ts timestamp without time zone default current_timestamp);
CREATE TABLE
postgres=# insert into test values (1) returning ts;
             ts             
----------------------------
 2013-05-17 14:54:33.072725
(1 row)

但那使用当地时间。试图强制将其转换为UTC会导致语法错误:

postgres=# create temporary table test(id int, ts timestamp without time zone default now() at time zone 'utc');
ERROR:  syntax error at or near "at"
LINE 1: ...int, ts timestamp without time zone default now() at time zo...

当前回答

这是两个等价解:

(在下面的代码中，你应该用'UTC'代替zone，用now()代替时间戳)

timestamp AT TIME ZONE ZONE -符合sql标准时区(zone, timestamp) -可以说可读性更强

函数timezone(zone, timestamp)等价于符合sql的构造timestamp AT TIME zone zone。

解释:

zone can be specified either as a text string (e.g., 'UTC') or as an interval (e.g., INTERVAL '-08:00') - here is a list of all available time zones timestamp can be any value of type timestamp now() returns a value of type timestamp (just what we need) with your database's default time zone attached (e.g. 2018-11-11T12:07:22.3+05:00). timezone('UTC', now()) turns our current time (of type timestamp with time zone) into the timezonless equivalent in UTC. E.g., SELECT timestamp with time zone '2020-03-16 15:00:00-05' AT TIME ZONE 'UTC' will return 2020-03-16T20:00:00Z.

文档:时区()

2018-11-11 07:40:29

其他回答

还有另一个解决方案:

timezone('utc', now())

2018-01-05 19:46:29

将其包装在函数中:

create function now_utc() returns timestamp as $$
  select now() at time zone 'utc';
$$ language sql;

create temporary table test(
  id int,
  ts timestamp without time zone default now_utc()
);

2013-05-17 13:04:15

甚至不需要函数。只需要在默认表达式周围加上括号:

create temporary table test(
    id int, 
    ts timestamp without time zone default (now() at time zone 'utc')
);

2013-05-17 13:30:51

函数已经存在: 时区(UTC的::文本,现在())

2018-08-22 11:26:42