Hive: how to convert millisecond timestamps?

Question

I am trying to use the HIVE UDFs (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions) from Sparklyr to read-in properly some timestamps.

Unfortunately, I have not been able to parse correctly the following timestamp:

unix_timestamp('2011-03-01T00:00:04.226Z', 'yyyy-MM-ddThh:mm:ss.SSS' )

returns NAs..

Any ideas? What is the right pattern here? THanks!

philantrovert · Accepted Answer · 2017-06-20T14:46:02.160

3

You need to quote T and Z

hive> select unix_timestamp('2011-03-01T00:00:04.226Z', "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'" );
OK
1298959204

Or try this if you are not afraid to be clumsy:

select unix_timestamp(cast(regexp_replace('2011-03-01T00:00:04.226Z', '(\\d{4})-(\\d{2})-(\\d{2})T(\\d{2}):(\\d{2}):(\\d{2}).(\\d{3})Z', '$1-$2-$3 $4:$5:$6.$7' ) as timestamp))

To convert from EST to UTC, use the following:

hive> select to_utc_timestamp(unix_timestamp('2011-03-01T00:00:04.226Z', "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'" )*1000, 'EST');
OK
2011-03-01 05:00:04

Multiplication with 1000 is required because, from Hive Language Manual:

Fractional values are considered as seconds. Integer values are considered as milliseconds.. E.g to_utc_timestamp(2592000.0,'PST'), to_utc_timestamp(2592000000,'PST') and to_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') all return the timestamp 1970-01-31 00:00:00

edited Jun 20 '17 at 14:46

answered Jun 20 '17 at 13:48

philantrovert

9,904
3
37
61

that returns a bigint type. Do you know why I cant use `to_utc_timestamp(mytime, 'EST')` where mytime is the `unix_timestamp` that we just created? how would you convert that to a timestamp type? – ℕʘʘḆḽḘ Jun 20 '17 at 14:08
assuming the orginal timestamp is in `EST` and I want it in `GMT` – ℕʘʘḆḽḘ Jun 20 '17 at 14:13
thanks! last quick question: why Am I losing the millisecond part in the timestamp? – ℕʘʘḆḽḘ Jun 20 '17 at 14:30
actually I get `1298955604` when I use `unix_timestamp`. Why is that? – ℕʘʘḆḽḘ Jun 20 '17 at 14:34
1

@Noobie I think because that's how the `unix_timestamp` function works. – philantrovert Jun 20 '17 at 14:36
No I mean my `bigint` is different than yours! :) – ℕʘʘḆḽḘ Jun 20 '17 at 14:37
1

Not quite sure how that is happening. You're losing milliseconds and I'm not? That's strange. :D – philantrovert Jun 20 '17 at 14:41
nice ! can you just explain what the `regex` syntax is doing? I dont get the `$` thing – ℕʘʘḆḽḘ Jun 20 '17 at 14:55
1

Whatever is between parantheses will be treated as one group so `(\\d{4})` at the start is group 1 which I am accessing using `$1`. So on and so forth. – philantrovert Jun 20 '17 at 14:58
1

Also, the difference you were getting while using `unix_timestamp` was most probably due to the difference in our timezones. – philantrovert Jun 20 '17 at 15:03
Please note that you are loosing the milliseconds (I wrote this part in the manual :-)) – David דודו Markovitz Jun 20 '17 at 15:20
cast as timestamp and again cast as double and substract. see https://stackoverflow.com/questions/18737561/how-do-i-get-millisecond-precision-in-hive – InLaw Nov 12 '18 at 16:21
unix_timestamp(, ) * 1000 to milliseconds refs: https://currentmillis.com/ Hive* unix_timestamp() * 1000 – Colin Wang Aug 10 '20 at 07:35

Hive: how to convert millisecond timestamps?

1 Answers1