TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.

[bhaggyit]

select TO_TIMESTAMP_TZ('20131714001400','YYYYMMDDHH24MISS') AT TIME ZONE 'UTC';
timezone
---------------------
2014-05-16 07:14:00
(1 row)


Could someone advise as to why Vertica is not failing as the Date is invalid ? "MM" component value is 17, however Vertica still transforms it to an inexplicable date.

Regards,
Bugs.

[NorbertKrupa]

I don't know the why, but logically a month value of 17 = 1 year and 5 months so that 1 year is added to the year component.

[JimKnicely]

Interesting. The TO_DATE function in 7.0.1 works the same. Notice how the months are converted to years (i.e. 13 = 1 year 1 month, 14 = 1 year 2 months)...

Code: Select all

dbadmin=> select to_date('201412', 'YYYYMM');
  to_date
------------
 2014-12-01
(1 row)

dbadmin=> select to_date('201413', 'YYYYMM');
  to_date
------------
 2015-01-01
(1 row)

dbadmin=> select to_date('201414', 'YYYYMM');
  to_date
------------
 2015-02-01
(1 row)

I guess this is a feature? Personally, I'd prefer that the date function would return an error. What do you guys think?

[beth]

Following your example, Jim, how does Vertica get March 3 here?

Code: Select all

dbadmin=> select to_date('201415', 'YYYYMM');
  to_date
------------
 2015-03-03
(1 row)

[NorbertKrupa]

As I mentioned in the first post, any months over 12 are carried over to the year component.

[beth]

I agree, but I would expect to_date('201415', 'YYYYMM') to be 2015-03-01 not 2015-03-03...

[NorbertKrupa]

Ah, I missed that. Yes, that is strange, but since you're passing incorrect values I wouldn't expect the result to make much sense.