需要帮助确定实际日期时间格式

Question

几天来，我一直在努力确定样本输入日期的正确格式：2020-01-30 14:39:25.022000 +00:00:00

背景：
我正在使用postgres进行数据迁移。并且，已经发现在DB中，与日期时间相关的字段(如"modifiedAt"(的数据类型为varchar，这有点奇怪。这些记录具有上述格式的值。

我已经在新的postgres数据库实例中修复了它，数据类型为timestamp。迁移数据后，这些字段以以下格式显示日期时间：

2020-01-30 14:39:25

我如何格式化上面的日期，以这种日期格式2020-01-30 14:39:25.022000 +00:00:00输出？

任何帮助都将不胜感激。

Answer 1

首先要使用timestamptz而不是timestamp。timestamptz实际上并没有存储时区，它只是让值知道时区。有关此方面的更多信息，请参阅：

时间戳8.5.1.3。时间戳。

其次，看起来你做了类似timestamp(0)的事情，将精度降低到了整秒。如果你不考虑精度，你会得到：

select '2020-01-30 14:39:25.022000 +00:00:00'::timestamptz;
timestamptz         
-----------------------------
01/30/2020 06:39:25.022 PST
-- I am in PST so the value gets rotated to that time zone for display.
-- If you want something closer to you desired output, then:
select to_char('2020-01-30 14:39:25.022000 +00:00:00'::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.USOF');
to_char            
-------------------------------
2020-01-30 14:39:25.022000+00

Answer 2

虽然时间戳字符串似乎有时区组件，但它是+00，这是时间戳的默认值。因此，您可以在这里使用TO_TIMESTAMP，并使用覆盖微秒的适当格式掩码：

WITH yourTable AS (
SELECT '2020-01-30 14:39:25.022123 +00:00:00'::text AS ts
)
SELECT TO_TIMESTAMP(LEFT(ts, 26), 'YYYY-MM-DD HH24:MI:SS.US')
FROM yourTable;

演示

上面演示的输出值是2020-01-30 14:39:25.022123+00