I'm stumped by a tricky issue regarding time zone changes from daylight savings to non daylight savings.
I'm trying to generate a series of timestamps, 6 hrs apart. This is later joined with data with corresponding timestamps at the 00, 06, 12, 18 hrs for each day in the dataset.
This works fine normally, using:
generate_series(extract(epoch from start_ts)::integer, extract(epoch from end_ts)::integer, 21600)
where start_ts is 00 hr on the first date, and end_ts is 00 hr on the last date exclusive.
However, when timezone offset goes from +11 to +10 half way through the series, it will no longer match any records since the series elements become 1 hr off.
Does anyone have suggestions on how to generate a series of 'epoch integers' or timestamps which would match 00,06,12,18 hr timestamps while respecting the timezone's offset?
This will generate it (using PostgreSQL 9.5+), starting from today and for 10 days:
select (current_date::timestamp + ((a-1)||' days')::interval)::timestamptz
from generate_series(1, 10, .25) a
Test it on a whole year:
select *, date_part('hour', d::timestamp), d::timestamp
from (
select (current_date::timestamp + ((a-1)||' days')::interval)::timestamptz AS d
from generate_series(1, 365, .25) a
) x
where date_part('hour', d) not in (0, 6, 12, 18)
Edit: The version below works with versions of PostgreSQL older than 9.5:
select (current_date::timestamp + (((a-1)/4.0)||' days')::interval)::timestamptz
from generate_series(1, 4* 10 ) a -- 10 days
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With