dst - PostgreSQL: Compute number of hours in the day on daylight savings time -

dst - PostgreSQL: Compute number of hours in the day on daylight savings time -

i'm trying come query count there 25 hours on daylight savings. table has column of type timestampz called hourly_timestamp. wrong reply have far looks this:

select extract(epoch tomorrow-today)/3600 from( select date_trunc('day', timezone('america/new_york', hourly_timestamp) today , date_trunc('day', timezone('america/new_york', hourly_timestamp))) + '1 day'::interval tomorrow )t;

when query executed during daylight savings time, still 24 hours , not 25. ideas how correctly?

the number of hours varies clock.

with hours ( select (timestamp time zone '2014-11-01 00:00:00 america/new_york' + (n || ' hour')::interval) hourly_timestamp generate_series(0, 72) n ) select hourly_timestamp , hourly_timestamp + interval '1' day one_day_later , hourly_timestamp + interval '1' day - hourly_timestamp elapsed_time hours; hourly_timestamp one_day_later elapsed_time -- [snip] 2014-11-01 22:00:00-04 2014-11-02 22:00:00-05 1 day 01:00:00 2014-11-01 23:00:00-04 2014-11-02 23:00:00-05 1 day 01:00:00 2014-11-02 00:00:00-04 2014-11-03 00:00:00-05 1 day 01:00:00 2014-11-02 01:00:00-04 2014-11-03 01:00:00-05 1 day 01:00:00 2014-11-02 01:00:00-05 2014-11-03 01:00:00-05 1 day 2014-11-02 02:00:00-05 2014-11-03 02:00:00-05 1 day 2014-11-02 03:00:00-05 2014-11-03 03:00:00-05 1 day 2014-11-02 04:00:00-05 2014-11-03 04:00:00-05 1 day [snip]

note 01:00 repeats, different offset. daylight savings time ends @ 02:00, clocks fall , repeat hr between 01:00 , 02:00, since daylight savings time has ended, there 5 hours between utc , america/new_york time zones.

this similar query displays dates, not timestamps.

with dates ( select (timestamp time zone '2014-11-01 00:00:00 america/new_york' + (n || ' day')::interval) daily_timestamp generate_series(0, 2) n ) select daily_timestamp::date , (daily_timestamp + interval '1' day)::date one_day_later , daily_timestamp + interval '1' day - daily_timestamp elapsed_time dates; daily_timestamp one_day_later elapsed_time -- 2014-11-01 2014-11-02 1 day 2014-11-02 2014-11-03 1 day 01:00:00 2014-11-03 2014-11-04 1 day

where did go wrong? calculating elapsed time after truncated time information. (dates don't have time zones associated them.) if take sec query , cast "daily_timestamp" date in mutual table expression, 24 hours, too.

with dates ( select (timestamp time zone '2014-11-01 00:00:00 america/new_york' + (n || ' day')::interval)::date daily_timestamp generate_series(0, 2) n ) select daily_timestamp::date , (daily_timestamp + interval '1' day)::date one_day_later , daily_timestamp + interval '1' day - daily_timestamp elapsed_time dates; daily_timestamp one_day_later elapsed_time -- 2014-11-01 2014-11-02 1 day 2014-11-02 2014-11-03 1 day 2014-11-03 2014-11-04 1 day

postgresql dst