timestamp+interval across a DST boundary gives erroneous result

Bug #1659032 reported by Raphael Mankin
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
postgresql-common (Ubuntu)
Invalid
Undecided
Unassigned

Bug Description

I am adding INTERVAL '1 day' to TIMESTAMP WITH TIME ZONE '2017-03-25 12:00 Europe/London'. This timestamp with chosen quite bloody mindedly as the arithmetic crosses the DST boundary. (I am testing other software that generates SQL).

Oracle, Vertica and Presto (with a pg back-end) all give a result of 13:00 on 26March; Raw Postgres give 12:00 on 26March, i.e. it adds only 23 hours. Changing INTERVAL '1 day' to INTERVAL '24 hours' yields 13:00.

demo_db=> SELECT TIMESTAMP with time zone '2017-03-25 12:00:00 Europe/London' + interval '1 day' ;
        ?column?
------------------------
 2017-03-26 12:00:00+01
(1 row)

demo_db=> SELECT TIMESTAMP with time zone '2017-03-25 12:00:00 Europe/London' + interval '24 hours' ;
        ?column?
------------------------
 2017-03-26 13:00:00+01
(1 row)

PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit

ProblemType: Bug
DistroRelease: Ubuntu 16.10
Package: postgresql 9.5+176+git1
ProcVersionSignature: Ubuntu 4.8.0-34.36-generic 4.8.11
Uname: Linux 4.8.0-34-generic x86_64
ApportVersion: 2.20.3-0ubuntu8.2
Architecture: amd64
CurrentDesktop: XFCE
Date: Tue Jan 24 15:01:08 2017
InstallationDate: Installed on 2017-01-15 (9 days ago)
InstallationMedia: Xubuntu 16.10 "Yakkety Yak" - Release amd64 (20161012.2)
PackageArchitecture: all
SourcePackage: postgresql-common
UpgradeStatus: No upgrade log present (probably fresh install)

Revision history for this message
Raphael Mankin (raph-p) wrote :
Revision history for this message
dino99 (9d9) wrote :

This is an unsupported release now. Please think to install the next LTS 'Bionic 18.04'

http://cdimage.ubuntu.com/ubuntu-next/daily-live/current/
https://www.omgubuntu.co.uk/2018/02/ubuntu-18-04-minimal-install-option

Changed in postgresql-common (Ubuntu):
status: New → Invalid
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.