hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Grover <mgro...@oanda.com>
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 mapred.child.java.opts
in hive-site.xml with a value like (verify your own timezone from http://en.wikipedia.org/wiki/List_of_tz_database_time_zones):
-Duser.timezone=America/Los_Angeles

Good luck!
Mark

----- Original Message -----
From: "Abhishek Pratap Singh" <manu.infy@gmail.com>
To: user@hive.apache.org
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. 





~Abhishek 




On Thu, Dec 1, 2011 at 10:21 AM, sonia gehlot < sonia.gehlot@gmail.com > 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, 

Sonia 


Mime
View raw message