Timestamp string conversion / from_utc

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

CodePudding user response：

from_utc_timestamp can accept timestamp or compatible string (yyyy-MM-dd HH:mm:ss.S), or bigint, not this: "yyyy-MM-dd'T'HH:mm:ssXXX"
Hive timestamps are timezoneless. Once you converted from UTC to America/NY, the timezone information is lost, only you know in which timezone it is, having timestamp converted it is already impossible to derive the timezone from it.
You can concatenate with timezone, conversion like this returns what you need but it works for particular date only. In December -05:00 timezone should be usedm not 04:00:

date_format(from_utc_timestamp('2021-10-03 15:10:00.0',"America/New_York"),"yyyy-MM-dd'T'HH:mm:ss 04:00") --This is wrong!!!

From_utc_timestamp is Daylight saving aware. It can be -05:00 or -04:00 depending on the date.

Consider this example, first returns 5, second returns 4:

select (unix_timestamp("2020-01-01 12:00:00.0")-unix_timestamp(from_utc_timestamp("2020-01-01 12:00:00.0","America/New_York")))/60/60

select (unix_timestamp("2020-10-19 12:00:00.0")-unix_timestamp(from_utc_timestamp("2020-10-19 12:00:00.0","America/New_York")))/60/60

So, you can get current time zone corresponding to America/New_York for the same timestamp and concatenate it with converted timestamp:

select concat(date_format(from_utc_timestamp('2021-10-03 15:10:00.0',"America/New_York"),"yyyy-MM-dd'T'HH:mm:ss"),' 0',
              --get hrs shift 
              (unix_timestamp("2021-10-03 15:10:00.0")-unix_timestamp(from_utc_timestamp("2021-10-03 15:10:00.0","America/New_York"))) div 3600,':00')

Result:

2021-10-03T11:10:00 04:00

It should work correctly with different timestamps taking into account daylight saving time for America/New_York.