Date time arithmetic bug

lukasederlukaseder Registered User

 I'm running this simple query:

 

test=> select (timestamp '1970-01-01 00:00:00.0' + interval '2 month') "yy";
yy
---------------------
1970-03-02 00:00:00
(1 row)

 

The expected output would be 1970-03-01 00:00:00. The result is off by one day. Other databases (e.g. Oracle, PostgreSQL) return the correct date.

 

I'm using this database version:

 

test=> select version();
version
------------------------------------
Vertica Analytic Database v7.1.1-0
(1 row)

Comments

  • Adrian_Oprea_1Adrian_Oprea_1 Registered User

    I think is because the interval + 1 month  will ad 30 days and there were 31 days in that month.

    ([email protected]:5433) [dbadmin] *> SELECT DATEDIFF('day', TO_DATE('01-01-1970','MM-DD-YYYY'), TO_DATE('02-01-1970','MM-DD-YYYY'));
    datediff
    ----------
    31
    (1 row)

    As seen here  in my example i used de month of feb of 2015 with the same formula as you 

    ([email protected]:5433) [dbadmin] *> select (timestamp '2015-02-01 00:00:00.0' + interval '1 months') "yy";
    yy
    ---------------------
    2015-03-03 00:00:00
    (1 row)

    - see that i get 30 days added to the date

     

    The result should be something like this , where i have to use the exact number of days.

    ([email protected]:5433) [dbadmin] *> select (timestamp '2015-02-01 00:00:00.0' + interval '28 days') "yy";
    yy
    ---------------------
    2015-03-01 00:00:00
    (1 row)

     This is strange or maybe we are not using it the way it should be used :).

    Or maybe is a bug ! 

     

  • lukasederlukaseder Registered User

    You're right about the 30 days:

     

    test=> select timestamp '1970-01-01 00:00:00.0' + interval '1 month';
    ?column?
    ---------------------
    1970-01-31 00:00:00
    (1 row)

     

    That's certainly not very useful, though :-), e.g.

     

    test=> select timestamp '1970-01-01 00:00:00.0' + interval '18 month';
    ?column?
    ---------------------
    1971-06-25 00:00:00
    (1 row)

     

    Things get worse with other date parts, e.g. year:

     

    test=> select timestamp '1970-01-01 00:00:00.0' + interval '20 year';
    ?column?
    ---------------------
    1989-12-27 00:00:00
    (1 row)

     

    Looks like some hard-coded 365 constant, somewhere

  • lukasederlukaseder Registered User

    Hmm... In fact, when using the SQL standard YEAR TO MONTH interval notation, things work as expected:

     

    test=> select timestamp '1970-01-01 00:00:00.0' + interval '20' year to month;
    ?column?
    ---------------------
    1990-01-01 00:00:00
    (1 row)

    test=> select timestamp '1970-01-01 00:00:00.0' + interval '0-1' year to month;
    ?column?
    ---------------------
    1970-02-01 00:00:00
    (1 row)

     

    That'll do for a workaround. Still wondering why the other, non-standard notation didn't work

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file