Why TO_UTC_TIMESTAMP in hive returns wrong year?

Question

Trying to run a simple query in hive CLI -

select to_utc_timestamp(unix_timestamp("04/24/2017 01:59:01","MM/dd/YYYY HH:mm:ss"),'EST');

result:

OK
1970-01-18 09:00:35.941
Time taken: 0.448 seconds, Fetched: 1 row(s)

Shouldn't the output be just date plus 4 hours?

`to_utc_timestamp` expects an actual timestamp as the input, which is not what you get from `unix_timestamp`. `to_utc_timestamp("2017-04-24 01:59:01","EST")` works just fine. — Andrew, Jun 07 '17 at 20:34
Thats not true. It accepts UNIX time stamp i.e bigint too. It says in hive documentation `{any primitive type}* = including timestamp/date, tinyint/smallint/int/bigint, float/double, decimal. E.g. to_utc_timestamp(0.123,'PST') returns 1970-01-01 08:00:00.123` — Pushkr, Jun 07 '17 at 20:41
Originally the documentation referred only to timestamp arguments. What you are quoting was my recent addition to the documentation. However, it seems things are a little bit more complected. I have just updated the documentation https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions — David דודו Markovitz, Jun 07 '17 at 22:18

David דודו Markovitz · Accepted Answer · 2017-06-07T22:19:37.487

1

yyyy (and not YYYY)
unix_timestamp returns bigint and to_utc_timestamp considers integer values as milliseconds

select to_utc_timestamp(1000*unix_timestamp("04/24/2017 01:59:01","MM/dd/yyyy HH:mm:ss"),'EST');

edited Jun 07 '17 at 22:19

answered Jun 07 '17 at 21:18

I wonder if there's any advantage to doing it one way vs the other? Timestamps in hive, ugh... – Andrew Jun 07 '17 at 22:36
1

Just to calirfy, the reason you need to use lower case `yyyy` is because java treats capital `Y` as a week year https://stackoverflow.com/questions/8686331/y-returns-2012-while-y-returns-2011-in-simpledateformat – Daniel Dror Mar 04 '18 at 08:58
@DanielDubovski, take a look at this lousy Java documentation https://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html – David דודו Markovitz Mar 04 '18 at 20:03

score 0 · Answer 2 · answered Jun 07 '17 at 20:54

0

Here we go. You need to wrap your unix_timestamp in a from_unixtime.

select to_utc_timestamp(from_unixtime(unix_timestamp('04/24/2017 01:59:01','MM/dd/yyyy hh:mm:ss'),'yyyy-MM-dd hh:mm:ss'),"EST");

answered Jun 07 '17 at 20:54

Andrew

2 Answers2