如何将bigint（以毫秒为单位的时间戳）值写入PostgreSQL中的时间戳

Question

我正在尝试将我的值存储在带有时区的时间戳字段中。它是从1970年以来的毫秒数。

select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')

期望得到30/5/2014 11:29:42 10:54:41.230，但实际上得到了22/08/46379 23:27:02.000

- Clyde

---

查询语句 select TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'DD/MM/YYYY HH24:MI:SS') || '.' || (1401432881222%1000) 的风格是否良好？ 返回结果为 30/05/2014 06:54:41.222。 - Clyde

to_timestamp 已经考虑了毫秒，因此它接受浮点数 Unix 时间戳。如果您想存储真正的时间戳，请只需存储其返回值。如果您想格式化它，请使用 MS 显示毫秒。 - pozs

但是在我的例子中（选择TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')），我正在按照您所说的做。 - Clyde

Answer 1

Unix时间戳通过秒来测量时间，而不是毫秒（几乎在所有地方都是如此，包括PostgreSQL）。

因此，您需要调用：

SELECT TO_TIMESTAMP(1401432881230 / 1000);

如果您想保留毫秒级别的精度，请使用 双精度 进行调用。

SELECT TO_TIMESTAMP(1401432881230::double precision / 1000);

- pozs

---

如果你希望的话，你也可以写成 TO_TIMESTAMP(1401432881230 / 1000.0)，这样就不需要使用显式转换了。 - mlc

使用双精度会引入舍入误差吗？ - KamilCuk

@KamilCuk 可以，但目前没有 to_timestamp(numeric) 变量 参见，无论使用什么参数，它都将被转换为 double precision。 - pozs

Answer 2

这是我如何将毫秒转换为时间戳并保留毫秒而不是秒。被接受的答案会丢失毫秒。

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;

- wener

Answer 3

好的，我明白了。我的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'))

我将带有毫秒的bigint-timestamp转换为所需格式（'YYYY-MM-DD HH24:MI:SS.MS'）的文本，并将其传递给to_timestamp函数。

- Clyde