hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Grover <>
Subject Re: Convert UTC timestamp to PST
Date Thu, 01 Dec 2011 19:30:48 GMT
Hi Sonia and Abhishek,
I wouldn't recommend using a hardcoded number (like 28800) for calculating time in PST/PDT.
That would bite you when daylight savings kick in and out.
If you look at the source code of the from_unixtime UDF, you will find that it uses the default
time zone set in the JVM.

There are options:
1) You can create your own UDF that converts UTC time to your format using a hardcoded timezone.
2) You can set the default timezone of the JVM and simply use regular from_unixlike UDF.

I would recommend the 2nd option. To do so, you can set the property named
in hive-site.xml with a value like (verify your own timezone from

Good luck!

----- Original Message -----
From: "Abhishek Pratap Singh" <>
Sent: Thursday, December 1, 2011 1:28:36 PM
Subject: Re: Convert UTC timestamp to PST

hi Sonia, 

Try this 

SELECT from_unixtime((unix_timestamp(TXN_DATE) - 28800), 'yyyy-MM-dd HH:MM:SS') as TXNDATE

28800 is the 8 hours difference in seconds for PDT. 


On Thu, Dec 1, 2011 at 10:21 AM, sonia gehlot < > wrote: 

Hi All, 

I have Unix timestamp in my table in UTC format. Is there is any inbuilt function to convert
it into PST or PDT in YYYY-MM-DD HH:MM:SS format? I know there are functions like from_unixtime
to convert unixtime to date format, but I am not sure how to convert it to PST. 

Thanks in advance, 


View raw message