如何在PostgreSQL中将bigint(以毫秒为单位的时间戳)值写入时间戳 [英] How to write bigint (timestamp in milliseconds) value as timestamp in postgresql
问题描述
我正在尝试将时区字段的值存储在时间戳中。从1970年以毫秒为单位。
选择TO_CHAR(TO_TIMESTAMP(1401432881230),'DD / MM / YYYY HH24:MI:SS.MS' )
预计 2014年5月30日11:29:42 10:54:41.230
,但获得
22/08/46379 23:27:02.000
Unix时间戳以秒为单位测量时间,不是毫秒(<
因此,您需要致电
选择TO_TIMESTAMP(1401432881230/1000);
如果要保留毫秒,请使用双精度:
选择TO_TIMESTAMP(1401432881230 :: double precision / 1000);
I'm trying to store in timestamp with timezone field my value. It is in milliseconds from 1970.
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
Unix timestamps measures time with seconds, and not milliseconds (almost everywhere, in PostgreSQL too).
Therefore you need to call
SELECT TO_TIMESTAMP(1401432881230 / 1000);
If you want to preserve milliseconds, call with double precision
:
SELECT TO_TIMESTAMP(1401432881230::double precision / 1000);
这篇关于如何在PostgreSQL中将bigint(以毫秒为单位的时间戳)值写入时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!