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 |
+-----------------------+
Or
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
