Skip to main content

TIMESERIES ("gap filling"?) works not properly

  • September 12, 2013
  • 3 replies
  • 20 views
Daniel_Leybovic
Forum|alt.badge.img+1
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)

    3 replies

    Daniel_Leybovic
    Forum|alt.badge.img+1
    • Daniel_Leybovic
    • Author
    • Participating Frequently
    • September 7, 2014
    bump (actually for v7.1 too)
    Arturs_Strauss
    • Arturs_Strauss
    • New Participant
    • January 23, 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
    Forum|alt.badge.img
    • id10t
    • Participating Frequently
    • January 23, 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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement