The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How to do GFI for timeseries in a timezone aware way, especially over daylight savings time.
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
0
Comments
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