BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16797
Logged by:          Dana Burd
Email address:      djburd@gmail.com
PostgreSQL version: 12.5
Operating system:   Ubuntu 20.04.1 LTS
Description:

EXTRACT(EPOCH FROM timestamp) should be using the local timezone - which can
be set in several ways, see documentation "8.5.3. Time Zones".  Here I use
SET TIME ZONE to set the local timezone for the client session.

-- Expected results (seen from PostgreSQL 9.1.11):

# SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
00:00:00'::timestamp));
SET
 date_part 
-----------
     18000
(1 row)

-- Results from PostgreSQL 12.5:

# SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
00:00:00'::timestamp));
SET
 date_part 
-----------
         0
(1 row)

-- Additional ambiguity
-- Expected results (seem from PostgreSQL 9.1.11):
# SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
00:00:00'::timestamp)), extract(epoch from ('01/01/1970
00:00:00'::timestamptz)) where '01/01/1970 00:00:00'::timestamp =
'01/01/1970 00:00:00'::timestamptz;
SET
 date_part | date_part 
-----------+-----------
     18000 |     18000
(1 row)

-- Ambiguous results from PostgreSQL 12.5:
# SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
00:00:00'::timestamp)), extract(epoch from ('01/01/1970
00:00:00'::timestamptz)) where '01/01/1970 00:00:00'::timestamp =
'01/01/1970 00:00:00'::timestamptz;
SET
 date_part | date_part 
-----------+-----------
         0 |     18000
(1 row)

-- Documentation
https://www.postgresql.org/docs/12/datatype-datetime.html
8.5.1.3. Time Stamps
"Conversions between timestamp without time zone and timestamp with time
zone normally assume that the timestamp without time zone value should be
taken or given as timezone local time."

https://www.postgresql.org/docs/7.4/release-7-4.html
E.202. Release 7.4
E.202.3.7. Data Type and Function Changes
"Change EXTRACT(EPOCH FROM timestamp) so timestamp without time zone is
assumed to be in local time, not GMT (Tom)"

-- Server details
-- OS timezone (though this should not matter since local timezone is set in
the session):
OS timezone of server with PostgreSQL 12.5:
$ date +"%Z %z"
UTC +0000

-- PostgreSQL version
# SELECT version();
                                                               version
                                                        

-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

-- PostgreSQL installed from standard Ubuntu focal repos:
$ lsb_release -a
No LSB modules are available.
Distributor ID:    Ubuntu
Description:    Ubuntu 20.04.1 LTS
Release:    20.04
Codename:    focal

$ apt list --installed |grep -i postgres
postgresql-12/focal-updates,focal-security,now 12.5-0ubuntu0.20.04.1 amd64
[installed,automatic]
postgresql-client-12/focal-updates,focal-security,now 12.5-0ubuntu0.20.04.1
amd64 [installed,automatic]
postgresql-client-common/focal-updates,focal-security,now 214ubuntu0.1 all
[installed,automatic]
postgresql-common/focal-updates,focal-security,now 214ubuntu0.1 all
[installed,automatic]
postgresql/focal-updates,focal-security,now 12+214ubuntu0.1 all [installed]