postgres: generate series of timestamps respecting time zone

Question

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?

Answer 1

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