CODEWITHSUNDEEP
sqlhivetimestamphiveqlunix-timestamp
linrongbin
linrongbin

Reputation: 3371

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

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

Upvotes: 1

Views: 1087

Answers (2)

leftjoin
leftjoin

Reputation: 38335

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).

Upvotes: 1

CompEng
CompEng

Reputation: 7394

try this:

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

Upvotes: 2

Related Questions