Date time arithmetic bug

Comments

• Adrian_Oprea_1

  May 2015

  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 ! 

   

  0
• lukaseder

  May 2015

  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

  0
• lukaseder

  May 2015

  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

  0
• lukaseder

  May 2015

  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

  0