将UTC中的当前时间用作PostgreSQL中的默认值

问题描述

我有一列 TIMESTAMP WITHTIME TIMEZONE 类型的列，并希望将其默认设置为UTC的当前时间。在UTC中获取当前时间很容易：

I have a column of the TIMESTAMP WITHOUT TIME ZONE type and would like to have that default to the current time in UTC. Getting the current time in UTC is easy:

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

使用当前时间戳作为列：

As is using the current timestamp for a column:

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会导致语法错误：

But that uses local time. Trying to force that to UTC results in a syntax error:

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

推荐答案

甚至不需要一个函数。只需在默认表达式周围加上括号即可：

A function is not even needed. Just put parentheses around the default expression:

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

这篇关于将UTC中的当前时间用作PostgreSQL中的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！