How to convert "2019-11-02T20:18:00Z" to timestamp in HQL?

Question

I have datetime string "2019-11-02T20:18:00Z". How can I convert it into timestamp in Hive HQL?

Answer 1

try this:

select from_unixtime(unix_timestamp("2019-11-02T20:18:00Z", "yyyy-MM-dd'T'HH:mm:ss"))

Answer 2

If you want preserve milliseconds then remove Z, replace T with space and convert to timestamp:

select timestamp(regexp_replace("2019-11-02T20:18:00Z", '^(.+?)T(.+?)Z$','$1 $2'));

Result:

2019-11-02 20:18:00

Also it works with milliseconds:

 select timestamp(regexp_replace("2019-11-02T20:18:00.123Z", '^(.+?)T(.+?)Z$','$1 $2'));

Result:

2019-11-02 20:18:00.123

Using from_unixtime(unix_timestamp()) solution does not work with milliseconds. Demo:

  select from_unixtime(unix_timestamp("2019-11-02T20:18:00.123Z", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"));

Result:

2019-11-02 20:18:00

Milliseconds are lost. And the reason is that function unix_timestamp returns seconds passed from the UNIX epoch (1970-01-01 00:00:00 UTC).