How to do GFI for timeseries in a timezone aware way, especially over daylight savings time.

Chris_1

QUESTION: How to do GFI for timeseries in a timezone aware way, especially over daylight savings time. See below two queries that provide completely different results: one that uses a table and one that uses GFI as in the example docs. --set timezone set timezone 'America/New_York'; --select distinct hours between midnight and midnight on Nov 3, daylight savings time (1am happens twice) --tdate is timestamptz datatype. select distinct to_char(time_slice(tdate, 1, 'hour')::timestamptz,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') ts from demo.msg_events where tdate between '2013-11-03T00:00' and '2013-11-04T00:00' order by ts; --25 hours are returned as expected. from 00:00:00-04:00 to 00:00-05:00 2013-11-03T00:00:00-04:00 2013-11-03T01:00:00-05:00 2013-11-03T02:00:00-05:00 2013-11-03T03:00:00-05:00 2013-11-03T04:00:00-05:00 2013-11-03T05:00:00-05:00 2013-11-03T06:00:00-05:00 2013-11-03T07:00:00-05:00 2013-11-03T08:00:00-05:00 2013-11-03T09:00:00-05:00 2013-11-03T10:00:00-05:00 2013-11-03T11:00:00-05:00 2013-11-03T12:00:00-05:00 2013-11-03T13:00:00-05:00 2013-11-03T14:00:00-05:00 2013-11-03T15:00:00-05:00 2013-11-03T16:00:00-05:00 2013-11-03T17:00:00-05:00 2013-11-03T18:00:00-05:00 2013-11-03T19:00:00-05:00 2013-11-03T20:00:00-05:00 2013-11-03T21:00:00-05:00 2013-11-03T22:00:00-05:00 2013-11-03T23:00:00-05:00 2013-11-04T00:00:00-05:00 --When trying to do GFI using TIMESERIES clause the results are different -- 1. the timezone offset is not correct -- 2. there are 26 hours returned select to_char(tstz::timestamptz,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') ts from ( select '2013-11-03 00:00'::timestamptz as ts1 from dual union select '2013-11-04 00:00'::timestamptz from dual ) t timeseries tstz as '1 hour' over(order by ts1); -- 26 hours are incorrectly returned from 04:00:00-05:00 to 05:00:00-05:00 2013-11-03T04:00:00-05:00 2013-11-03T05:00:00-05:00 2013-11-03T06:00:00-05:00 2013-11-03T07:00:00-05:00 2013-11-03T08:00:00-05:00 2013-11-03T09:00:00-05:00 2013-11-03T10:00:00-05:00 2013-11-03T11:00:00-05:00 2013-11-03T12:00:00-05:00 2013-11-03T13:00:00-05:00 2013-11-03T14:00:00-05:00 2013-11-03T15:00:00-05:00 2013-11-03T16:00:00-05:00 2013-11-03T17:00:00-05:00 2013-11-03T18:00:00-05:00 2013-11-03T19:00:00-05:00 2013-11-03T20:00:00-05:00 2013-11-03T21:00:00-05:00 2013-11-03T22:00:00-05:00 2013-11-03T23:00:00-05:00 2013-11-04T00:00:00-05:00 2013-11-04T01:00:00-05:00 2013-11-04T02:00:00-05:00 2013-11-04T03:00:00-05:00 2013-11-04T04:00:00-05:00 2013-11-04T05:00:00-05:00

Chris_1

the fix was to add "+00" to the timestamps that are passed in

Daniel_Leybovic

Hi Chris!

Still be careful with gap filling.
I don't sure  that it's a bug or some strange formula, we still didn't get an answer, so it's looks like bug.
https://community.vertica.com/vertica/topics/timeseries_gap_filling_works_not_properly
https://community.vertica.com/vertica/topics/timeseries_return_strange_results

daniel=> \set min       '''2013-02-26 00:00:00+00'''
daniel=> \set max       '''2013-09-11 00:00:00+00'''
daniel=> \set interval  '''14 DAYS 12 HOURS 47 MINUTES'''
daniel=> \e
         ts
---------------------
 2013-02-16 18:30:00
 2013-03-03 07:17:00
 2013-03-17 20:04:00
 2013-04-01 08:51:00
 2013-04-15 21:38:00
 2013-04-30 10:25:00
 2013-05-14 23:12:00
 2013-05-29 11:59:00
 2013-06-13 00:46:00
 2013-06-27 13:33:00
 2013-07-12 02:20:00
 2013-07-26 15:07:00
 2013-08-10 03:54:00
 2013-08-24 16:41:00
 2013-09-08 05:28:00
(15 rows)
daniel=> \p
SELECT ts
  FROM (SELECT '2013-02-26 00:00:00+00'::TIMESTAMPTZ as tm
        UNION
        SELECT '2013-09-11 00:00:00+00'::TIMESTAMPTZ as tm) as t
TIMESERIES ts as '14 DAYS 12 HOURS 47 MINUTES' OVER (ORDER BY tm);