You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Jay Green Stevens <jg...@hotels.com> on 2019/12/20 15:48:00 UTC

Timestamp format

Hi All,



We’re having an issue when trying to extract the hour from a timestamp.



The (string) timestamps in one of our tables is “2019-12-09T19:56:55.857916Z”, but in all others its “2019-09-10T14:45:34.520Z”. In the first there are 3 additional decimal places, so it means that when we try to extract the hour using hour(from_unixtime(UNIX_TIMESTAMP(table_timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd HH:mm:ss")) it gives the wrong hour because the table timestamp its receiving is not in the right format.



I’ve found that we can get the hour out using hour(cast(regexp_replace(table_timestamp, "T|Z", " ") as timestamp)) which works for the two kinds of formats that we’re getting from our tables.



I just wanted to see if anyone has dealt with this before, and if there are any better ideas for this?



Thanks in advance,



Merry Christmas!

Jay