Search code examples
hivehqlhiveql

Covert ZULU time to PST

I am trying to covert start_time which is in yulu format to pst. Start_time sample: 2020-02-04T04:36:42:211Z

from_unixtime(unix_timestamp(sub string(start_time,1,17),'yyyy-MM-ddThh:mm:ss.SSSZ),'yyyy-MM-dd hh:mm:ss)

But I am getting output as NULL.

Please help.

Solution

  • It is better to use FROM_UTC_TIMESTAMP because UNIX_TIMESTAMP returns seconds, you will lose the millisecond component of your timestamp.

    FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(2020-02-04T04:36:42:211Z, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'"), 'PST')

    Sometime it might possible that because of "T" and "Z" our result get distorted.In that case we can use:

    from_utc_timestamp(CONCAT(substring('2020-02-04T04:36:42:211Z',1,10)," ",substring('2020-02-04T04:36:42:211Z',12,12)),'PST')