1

I have a hive table in which the data with datatype timestamp is in UTC format. I have created a view on top of this table to convert UTC to CST with daylight savings. That conversion is working fine. But the data is missing milliseconds in it.

The below logic is used in view to convert UTC to CST

FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT')

Please help me to convert UTC to CST including the milliseconds in timestamp.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Amrutha K
  • 204
  • 1
  • 3
  • 13

1 Answers1

0

unix_timestamp() function ignores milliseconds. it returns seconds passed from unix epoch. Milliseconds are lost. For example:

 select FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2020-01-21 15:15:35.125', 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT');

returns:

2020-01-21 09:15:35

If the timestamp is in the right format 'yyyy-MM-dd HH:mm:ss.SSS' (and in your example, according to the template, it does not require conversion), this works fine:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select FROM_UTC_TIMESTAMP(s.added_date, 'CST6CDT')  from s;

Result: 

2020-01-21 09:15:35.125

And if the timestamp is in the format which requires conversion, the workaround is to extract milliseconds from original timestamp and add to the milliseconds you got after multiplying unix_timestamp by 1000:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000+cast(split(added_date,'\\.')[1] as int), 'CST6CDT')  from s;

Result:

2020-01-21 09:15:35.125

One more workaround is to concatenate with milliseconds:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select concat_ws('.',cast(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT') as string),split(added_date,'\\.')[1]) from s;

Result:

2020-01-21 09:15:35.125
leftjoin
  • 36,950
  • 8
  • 57
  • 116