Page 1 of 1
TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 3:54 am
by 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.
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 4:50 am
by 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.
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 12:29 pm
by 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?
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 12:57 pm
by 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)
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 1:19 pm
by NorbertKrupa
As I mentioned in the first post, any months over 12 are carried over to the year component.
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 4:04 pm
by beth
I agree, but I would expect to_date('201415', 'YYYYMM') to be 2015-03-01 not 2015-03-03...
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Posted: Wed May 07, 2014 5:42 pm
by 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.