How to convert a date string from UTC to a specific TimeZone in HIVE?

Question

My Hive table has a date column with UTC date strings. I want to get all rows for a specific EST date.

I am trying to do something like below:

Select * 
from TableName T
where TO_DATE(ConvertToESTTimeZone(T.date))  = "2014-01-12" 

I want to know if there is a function for ConvertToESTTimeZone or how can I achieve this?

I tried the following, but it does not work (my default time zone is CST):

TO_DATE(from_utc_timestamp(T.Date) = "2014-01-12" 
TO_DATE( from_utc_timestamp(to_utc_timestamp (unix_timestamp (T.date), 'CST'),'EST'))

Thanks in advance.

Update:

Strange behavior. When I do this:

select "2014-01-12T15:53:00.000Z", TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP("2014-01-12T15:53:00.000Z", "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'"), 'EST')) 
from TABLE_NAME T1
limit 3

I get

    _c0                          _c1
0   2014-01-12T15:53:00.000Z    1970-01-16
1   2014-01-12T15:53:00.000Z    1970-01-16
2   2014-01-12T15:53:00.000Z    1970-01-16

Answer 1

The CST system time zone does not matter for converting UTC to EST to Hive. You should get the right results with:

TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(T.date, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'") * 1000, 'EST'))

, UNIX_TIMESTAMP , .

Answer 2

CST :

to_date(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(eff_timestamp, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'") * 1000, 'CST6CDT'))