Thread: BUG #12739: to_timestamp function conver string to time incorrectly
BUG #12739: to_timestamp function conver string to time incorrectly
From
lstrupinskaya@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 12739 Logged by: Liliya Email address: lstrupinskaya@gmail.com PostgreSQL version: 9.3.6 Operating system: Windows Description: When trying to use to_timestamp function to convert string '3/10/2013 2:30' to timestamp it was actually converted into 3/10/2013 3:30. Below is select statement: select to_timestamp('3/10/2013 2:30','MM/DD/YYYY HH24:MI'); it seems that problem was caused by days when daylight time is switched. all following and preceding hours where properly converted.
lstrupinskaya@gmail.com writes: > When trying to use to_timestamp function to convert string '3/10/2013 2:30' > to timestamp it was actually converted into 3/10/2013 3:30. > Below is select statement: > select to_timestamp('3/10/2013 2:30','MM/DD/YYYY HH24:MI'); If you're running with USA DST rules, there was no such thing as 2:30 observed local time on that date: clocks advanced from 1:59:59 to 3:00:00. As documented, Postgres handles invalid or ambiguous times near a DST transition by supposing that the given time is local standard time. So in the New York zone for instance, 2:30 local standard time (GMT-5) would equate to 3:30 local daylight time (GMT-4), and daylight time would have been what was observed at that instant, so that's what gets printed. If you don't want DST-aware handling of timestamps, don't use type timestamptz nor to_timestamp (which actually produces timestamptz). regards, tom lane