[PG Upgrade Series] Extract Epoch Trap

最新推荐文章于 2023-12-18 16:16:04 发布

DBADaily

最新推荐文章于 2023-12-18 16:16:04 发布

阅读量156

点赞数

分类专栏： PostgreSQL 文章标签： postgresql database 数据库

本文链接：https://blog.csdn.net/DBADaily/article/details/123156951

版权

本文探讨了在从PostgreSQL 9.1升级到11时，从无时区的时间戳中提取Unix时间戳（Epoch）导致不同结果的问题。文章解释了这实际上是由于PostgreSQL从9.2版本开始区分了带时区和不带时区的时间戳，并提供了如何避免陷阱的建议。建议在提取Epoch时使用带时区的时间戳或将其转换为带时区的时间戳。

摘要由CSDN通过智能技术生成

Background

In many cases applications need to get current Unix timestamp (seconds since 1970-01-01 00:00:00 UTC, also called Unix epoch or Unix time or POSIX time) as the following Linux command shows.

# date -d '2020-01-01' +%s
1577808000

The Extract Epoch Issue

After upgrading PostgreSQL 9.1 to PostgreSQL 11, the same SQL produces different results.

SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);

In PostgreSQL 9.1,

postgres=# SELECT version();
                                                    version
------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.x on xxx
(1 row)
postgres=# SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);
 date_part  
------------
 1577808000
(1 row)
postgres=# SELECT to_timestamp(extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone));
      to_timestamp      
------------------------
 2020-01-01 00:00:00+08
(1 row)

However in PostgreSQL 11, the outcome is NOT same !

postgres=# SELECT version();
                                                    version
------------------------------------------------------------------------------------------------
 PostgreSQL 11.x on xxx
(1 row)
postgres=# SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);
 date_part  
------------
 1577836800
(1 row)
postgres=# SELECT to_timestamp(extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone));
      to_timestamp      
------------------------
 2020-01-01 08:00:00+08
(1 row)

Bug or Feature

After exploring PostgreSQL 9.1 and PostgreSQL 9.2 documentations, we can get some clues.

PostgreSQL 9.1 EXTRACT

epoch

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval

PostgreSQL 9.2 EXTRACT

epoch

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

As you can see, starting from PostgreSQL 9.2, it distinguishes between timestamp without time zone and timestamp with time zone, while PostgreSQL 9.1 not.

It makes sense as epoch is the seconds since 1970-01-01 00:00:00 UTC which is a timestamp with time zone.

When extracting epoch from a timestamp without time zone, strictly speaking, it should not use the word ‘epoch’ which always relates to UTC time zone.

Say, when extracting seconds from a timestamp without time zone, it will return the number of seconds since 1970-01-01 00:00:00 local time

Let’s try to explain the weird result in PostgreSQL 11 when extracting seconds from a timestamp without time zone based on the documentation.

As shown below, the offset is 28800 seconds, i.e. 8 hours, which is exactly the same utc_offset of the local time zone PRC.

postgres=# SELECT version();
                                                    version
----------------------------------------------------------------------------