Skip to main content

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

  • February 3, 2014
  • 2 replies
  • 17 views
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

    2 replies

    Chris_1
    • Chris_1
    • Author
    • New Participant
    • February 20, 2014
    the fix was to add "+00" to the timestamps that are passed in
    Daniel_Leybovic
    Forum|alt.badge.img+1
    • Daniel_Leybovic
    • Participating Frequently
    • February 21, 2014
    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);

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement