如何在 postgresql 中将 bigint（以毫秒为单位的时间戳）值写为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23950025/
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
How to write bigint (timestamp in milliseconds) value as timestamp in postgresql
提问by Clyde
I'm trying to store in timestamp with timezone field my value. It is in milliseconds from 1970.
我正在尝试使用时区字段将我的值存储在时间戳中。从 1970 年开始，以毫秒为单位。
select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')
select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')
Expected 30/5/2014 11:29:42 10:54:41.230, but get
22/08/46379 23:27:02.000
预期30/5/2014 11:29:42 10:54:41.230，但得到
22/08/46379 23:27:02.000
回答by pozs
Unix timestampsmeasures time with seconds, and notmilliseconds (almosteverywhere, in PostgreSQL too).
Unix 时间戳用秒来衡量时间，而不是毫秒（几乎无处不在，在 PostgreSQL 中也是如此）。
Therefore you need to call
因此你需要打电话
SELECT TO_TIMESTAMP(1401432881230 / 1000);
If you want to preserve milliseconds, call with double precision:
如果要保留毫秒，请使用double precision以下命令调用：
SELECT TO_TIMESTAMP(1401432881230::double precision / 1000);
回答by wener
This is how I convert ms to timestamp and keep ms instead seconds.The accepted answer will drop ms.
这就是我将毫秒转换为时间戳并保留毫秒而不是秒的方式。接受的答案将降低毫秒。
WITH ts AS (SELECT 1401432881230 AS ts)
SELECT to_timestamp(ts / 1000) + ((ts % 1000 ) || ' milliseconds') :: INTERVAL
FROM ts;
-- FOR ALTER COLUMN
ALTER TABLE my_info
ALTER COLUMN tstmp TYPE TIMESTAMP USING to_timestamp(tstmp / 1000) + ((tstmp % 1000) || ' milliseconds') :: INTERVAL;
回答by Clyde
Okay, I understood. My INSERT should looks like:
好的，我明白了。我的 INSERT 应该是这样的：
INSERT INTO events (timestamp) VALUES (to_timestamp(TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'YYYY-MM-DD HH24:MI:SS') || '.' || (1401432881222%1000), 'YYYY-MM-DD HH24:MI:SS.MS'))
INSERT INTO events (timestamp) VALUES (to_timestamp(TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'YYYY-MM-DD HH24:MI:SS') || '.' || (1401432881222%1000), 'YYYY-MM-DD HH24:MI:SS.MS'))
I'm converting bigint-timestamp with milliseconds to text with required format ('YYYY-MM-DD HH24:MI:SS.MS') and pass it to to_timestamp function.
我正在将毫秒的 bigint-timestamp 转换为具有所需格式的文本 ('YYYY-MM-DD HH24:MI:SS.MS') 并将其传递给 to_timestamp 函数。
