Search code examples
sqlhivetimestamphiveqlunix-timestamp

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?

Solution

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