在 UTC 中使用当前时间作为 PostgreSQL 中的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16609724/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using current time in UTC as default value in PostgreSQL
提问by Wichert Akkerman
I have a column of the TIMESTAMP WITHOUT TIME ZONEtype and would like to have that default to the current time in UTC. Getting the current time in UTC is easy:
我有一个该TIMESTAMP WITHOUT TIME ZONE类型的列，并希望将其默认为 UTC 中的当前时间。以 UTC 格式获取当前时间很容易：
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)
But that uses local time. Trying to force that to UTC results in a syntax error:
但这使用当地时间。试图将其强制为 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...
回答by Daniel Vérité
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')
);
回答by martti
Still another solution:
还有一个解决方案：
timezone('utc', now())
回答by Denis de Bernardy
Wrap it in a function:
将其包装在一个函数中：
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()
);
回答by Risadinha
What about
关于什么
now()::timestamp
If your other timestamp are without time zone then this cast will yield the matching type "timestamp without time zone" for the current time.
如果您的其他时间戳没有时区，则此转换将生成当前时间的匹配类型“无时区的时间戳”。
I would like to read what others think about that option, though. I still don't trust in my understanding of this "with/without" time zone stuff.
不过，我想了解其他人对该选项的看法。我仍然不相信我对“有/没有”时区内容的理解。
EDIT: Adding Michael Ekoka's comment here because it clarifies an important point:
编辑：在这里添加 Michael Ekoka 的评论，因为它阐明了一个重要的观点：
Caveat. The question is about generating default timestamp in UTC for a timestamp column that happens to not store the time zone (perhaps because there's no need to store the time zone if you know that all your timestamps share the same). What your solution does is to generate a local timestamp (which for most people will not necessarily be set to UTC) and store it as a naive timestamp (one that does not specify its time zone).
警告。问题是关于在 UTC 中为恰好不存储时区的时间戳列生成默认时间戳（可能是因为如果您知道所有时间戳共享相同，则无需存储时区）。您的解决方案所做的是生成一个本地时间戳（对于大多数人来说不一定会设置为 UTC）并将其存储为一个简单的时间戳（一个未指定其时区的时间戳）。
回答by lakesare
These are 2 equivalent solutions:
这是 2 个等效的解决方案：
(in the following code, you should substitute 'UTC'for zoneand now()for timestamp)
（下面的代码，你应该取代'UTC'的区和now()用于时间戳）
timestamp AT TIME ZONE zone- SQL-standard-conformingtimezone(zone, timestamp)- arguably more readable
timestamp AT TIME ZONE zone- 符合 SQL 标准timezone(zone, timestamp)- 可以说更具可读性
The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.
函数 timezone(zone, timestamp) 等效于符合 SQL 的构造时间戳 AT TIME ZONE zone。
Explanation:
解释：
- zonecan 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 - timestampcan 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 inUTC.
E.g.,SELECT timestamp with time zone '2020-03-16 15:00:00-05' AT TIME ZONE 'UTC'will return2020-03-16T20:00:00Z.
- zone可以指定为文本字符串（例如，
'UTC'）或间隔（例如，INTERVAL '-08:00'） - 这里是所有可用时区的列表 - 时间戳可以是时间戳类型的任何值
now()返回一个时间戳类型的值（正是我们需要的），并附加了数据库的默认时区（例如2018-11-11T12:07:22.3+05:00）。timezone('UTC', now())将我们的当前时间（带有时区的时间戳类型）转换为UTC.
例如，SELECT timestamp with time zone '2020-03-16 15:00:00-05' AT TIME ZONE 'UTC'将返回2020-03-16T20:00:00Z。
Docs: timezone()
文档：时区（）
回答by user10259440
Function already exists: timezone('UTC'::text, now())
函数已经存在：timezone('UTC'::text, now())
