Geek Answers Handbook

Postgres: generate a sequence of timestamps related to the time zone

I was surpassed by a difficult problem associated with changes in the time zone from summer savings to saving daylight.

I am trying to create a series of timestamps divided by 6 hours. Later it is combined with data with the corresponding time stamps at 00, 06, 12, 18 hours for each day in the data set.

This works fine, 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 exclusive last date.

However, when the time zone offset goes from +11 to +10 halfway through the series, it will no longer correspond to any records, since the elements of the series lose 1 hour.

Does anyone have any suggestions on how to generate a series of “era integers” or timestamps that will correspond to 00.06, 12.18 hours timestamps while respecting the time zone offset?

+4
source share
2 answers

This will generate it (using PostgreSQL 9.5+), starting today and for 10 days:

select (current_date::timestamp + ((a-1)||' days')::interval)::timestamptz
from generate_series(1, 10, .25) a

Test it for a 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)

Change . The next version works with PostgreSQL versions older than 9.5:

select (current_date::timestamp + (((a-1)/4.0)||' days')::interval)::timestamptz
from generate_series(1, 4*  10  ) a  -- 10 days
+2
source

@Ziggy answer is great, use this. however, here is how I solved this in my application, which cannot use decimals in generate_series (v9.4):

_min timestamp with time zone, -- the first timestamp in the series
_max timestamp with time zone, -- the last timestamp in the series
_inc integer, -- the increment in seconds, eg 21600 (6hr)
_tz text
  • creates a series from _max down using the tz offset _max,
  • _min , tz- _min,
  • , _inc tz , ,

:

select t1 from (
    select ser,
        to_timestamp(ser) t1,
        extract(epoch from
                to_timestamp(ser) at time zone _tz
                    - date_trunc('day', to_timestamp(ser) at time zone _tz)
            )::integer % _inc = 0 is_good
    from (
        select 'ser1' s, generate_series(extract(epoch from _min)::integer, extract(epoch from _max)::integer, _inc) ser
        union all
        select 'ser2' s, generate_series(extract(epoch from _max)::integer, extract(epoch from _min)::integer, _inc * -1) ser
    ) x
    group by ser, _tz, _inc
    order by ser asc
) x
where is_good 
;
0

More articles:


All Articles