Timestamp string conversion / from_utc_timestamp

AKRAM — Sun, 19 Dec 2021 16:55:07 GMT

I need to convert 2021-10-03 15:10:00.0 as 2021-10-03T15:10:00-04:00

I tried with.

from_utc_timestamp(from_unixtime(unix_timestamp('2021-10-03 15:10:00.0', "yyyy-MM-dd HH:mm:ss.S"),"yyyy-MM-dd'T'HH:mm:ssXXX"),"America/New_York")

I got Null value

Any suggestions please

Re: Timestamp string conversion / from_utc_timestamp

tarak271 — Wed, 05 Jan 2022 17:38:50 GMT

I have used CDP (Hive-3.1.3000) to run below queries.

1. I am getting value instead of null when i execute below command

0: jdbc:hive2://hs2> select from_utc_timestamp(from_unixtime(unix_timestamp('2021-10-03 15:10:00.0', "yyyy-MM-dd HH:mm:ss.S"),"yyyy-MM-dd'T'HH:mm:ssXXX"),"America/New_York"); +------------------------+ | _c0 | +------------------------+ | 2021-10-02 20:00:00.0 | +------------------------+

2. from_utc_timestamp does not give output along with TimeZone as it is internally returning LocalDateTime which does not have TZ information

GenericUDFFromUtcTimestamp.java

// Now output this timestamp's millis value to the equivalent toTz. Timestamp result = Timestamp.valueOf( fromTs.getZonedDateTime().withZoneSameInstant(toTz.toZoneId()).toLocalDateTime().toString());

LocalDateTime

//----------------------------------------------------------------------- /** * Outputs this date-time as a {@code String}, such as {@code 2007-12-03T10:15:30}. * <p> * The output will be one of the following ISO-8601 formats: * <ul> * <li>{@code uuuu-MM-dd'T'HH:mm}</li> * <li>{@code uuuu-MM-dd'T'HH:mm:ss}</li> * <li>{@code uuuu-MM-dd'T'HH:mm:ss.SSS}</li> * <li>{@code uuuu-MM-dd'T'HH:mm:ss.SSSSSS}</li> * <li>{@code uuuu-MM-dd'T'HH:mm:ss.SSSSSSSSS}</li> * </ul> * The format used will be the shortest that outputs the full value of * the time where the omitted parts are implied to be zero. * * @return a string representation of this date-time, not null */ @Override public String toString() { return date.toString() + 'T' + time.toString(); }

3. Converting timestamp to the format "2021-10-03T15:10:00-04:00" does not seem to be possible with existing Hive UDF functions instead 'Z' notation can be used

0: jdbc:hive2://hs2> select from_unixtime(unix_timestamp('2021-10-03 15:10:00.0', "yyyy-MM-dd HH:mm:ss.S"),"yyyy-MM-dd'T'HH:mm:ssXXX"); +-----------------------+ | _c0 | +-----------------------+ | 2021-10-03T15:10:00Z | +-----------------------+

0: jdbc:hive2://hs2> select from_unixtime(unix_timestamp('2021-10-03 15:10:00.0', "yyyy-MM-dd HH:mm:ss.S"),"yyyy-MM-dd'T'HH:mm:ssZZZ"); +---------------------------+ | _c0 | +---------------------------+ | 2021-10-03T15:10:00+0000 | +---------------------------+

Reference: https://stackoverflow.com/questions/34723683/simpledateformat-ignores-xxx-if-timezone-is-set-to-utc

question Re: Timestamp string conversion / from_utc_timestamp in Support Questions

Timestamp string conversion / from_utc_timestamp

Re: Timestamp string conversion / from_utc_timestamp