TIMESERIES ("gap filling"?) works not properly

Daniel_Leybovic · September 2013

TIMESERIES: Why it starts from '2013-02-16'?

--- script for test(see results)  ------------------------------  \set min       '''2013-02-26 00:00:00'''  \set max       '''2013-09-11 00:00:00'''  \set interval  '''14 DAYS 12 HOURS 47 MINUTES'''    SELECT ts    FROM (SELECT :min::TIMESTAMP as tm          UNION          SELECT :max::TIMESTAMP as tm) as t  TIMESERIES ts as :interval OVER (ORDER BY tm);

--- results  ------------------------------           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)

Vertica version: Vertica Analytic Database v6.1.2-0 Brief explanation: 1. UNION selects two dates 'min'&'max' as column

daniel=> \p  SELECT '2013-02-26 00:00:00'::TIMESTAMP as tm          UNION  SELECT '2013-09-11 00:00:00'::TIMESTAMP as tm;  daniel=> \g           tm            ---------------------   2013-09-11 00:00:00   2013-02-26 00:00:00  (2 rows)

2. TIMESERIES should fill with constant predefined interval (final query) --- By example from here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#14431.htm (Creating a dense time series)

Daniel_Leybovic · September 2014

bump (actually for v7.1 too)

Arturs_Strauss · January 2015

Hi,

I also have come across problems with TIMESERIES functionality using a 1 MONTH time slice. And clearly it seems that this is a bug.

Here is simple SQL example:

with interval_source
as
(
SELECT to_date('201102','YYYYMM')  AS date_interval
UNION
SELECT to_date('201201','YYYYMM') AS date_interval
),
month_time_series 
as
(
SELECT cast(slice_time as date) AS date_by_month
  FROM interval_source a 
TIMESERIES slice_time AS '1 MONTH' OVER (ORDER BY cast(a.date_interval as timestamp))
 ORDER BY a.date_interval
)
select to_char(date_by_month,'YYYY-MM-DD') month_slice_interval,       datediff('dd',date_by_month,lead(date_by_month,1) over (order by date_by_month)) day_difference
 from month_time_series

In a result you would get:

month_slice_interval day_difference

2010-12-04 30

2011-01-03 30

2011-02-02 30

2011-03-04 30

2011-04-03 30

2011-05-03 30

2011-06-02 30

2011-07-02 30

2011-08-01 30

2011-08-31 30

2011-09-30 30

2011-10-30 30

2011-11-29 (null)

It seems that Vertica hasn’t taken in account that there are months in a year where are 31 day and a month such as February where sometimes there is 28 days or 29 days.

It can be clearly seen in August. There are two lines for August but should be only one.

Also it is not clear for me why the time slice starts with 1 month before my starting peridos and also it end 1 month before my end period.

By that I mean I am starting with 01-Jan-20011 but the time slice has started with 04-Dec-2010 and ending period is 01-Dec-2011 but it ends with 29-Nov-2011...?

It would be great if someone could give some reply for this problem.

Yes and it is for the for v7.1

id10t · January 2015

Hi!

Do not bother, Support don't care. Im too disappointed from Vertica Team. Such amazing product spoiled by a bad team.

Timesetries - bug
LAP - bug
unixODBC - bug with times
JDBC - poor performance
Sometimes RLE produces bugs (can't link on ticket)
Vertica Support - support is their last worries...

Do a conclusions

Regards

TIMESERIES ("gap filling"?) works not properly

Comments

Leave a Comment