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.
TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
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.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
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)...
I guess this is a feature? Personally, I'd prefer that the date function would return an error. What do you guys think?
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
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)
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
As I mentioned in the first post, any months over 12 are carried over to the year component.
Checkout vertica.tips for more Vertica resources.
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
I agree, but I would expect to_date('201415', 'YYYYMM') to be 2015-03-01 not 2015-03-03...
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: TO_TIMESTAMP_TZ..AT TIME ZONE 'UTC' - Issue.
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.
Checkout vertica.tips for more Vertica resources.