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
