postgresql Postgres 时间戳到 unix 时间（以毫秒为单位）作为 bigint
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25978350/
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
Postgres timestamp to unix time in milliseconds as a bigint
提问by sungiant
How can I get the following snippet to work in postgres:
如何让以下代码段在 postgres 中工作：
ALTER TABLE mytable
ADD COLUMN create_time_utc bigint not null
DEFAULT (now() at time zone 'utc');
I want the new column create_time_utcto be the unix time in milliseconds (i.e number of milliseconds since Unix epoch January 1 1970).
我希望新列create_time_utc是以毫秒为单位的 Unix 时间（即自 Unix 纪元 1970 年 1 月 1 日以来的毫秒数）。
I know I need to convert the postgres timestampto a bigint, but I'm not sure how to do that.
我知道我需要将 postgres 转换timestamp为 bigint，但我不知道该怎么做。
回答by Clodoaldo Neto
extract(epoch
extract(epoch
alter table mytable
add column create_time_utc bigint not null
default (extract(epoch from now()) * 1000);
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
