Date time arithmetic bug

lukaseder

 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)

Adrian_Oprea_1

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

(dbadmin@: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 

(dbadmin@: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.

(dbadmin@: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 ! 

 

lukaseder

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

lukaseder

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

lukaseder

For future visitors, the documentation is here:

http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/Date-Time/INTERVAL.htm