hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sam William <sa...@stumbleupon.com>
Subject Re: Convert UTC timestamp to PST
Date Thu, 01 Dec 2011 21:31:46 GMT
  I have faced this issue as well .  We have  our timezones set to UTC on the machines in the
cluster, but we like to see UTC times and PT  times  for different purposes.  Currently, I
have a custom UDF that serves our needs . I have always thought, it would be great if from_unixtime
takes an optional TZ string parameter .   What do you guys think ??


On Dec 1, 2011, at 11:30 AM, Mark Grover wrote:

> 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 
> 

Sam William
sampd@stumbleupon.com




Mime
View raw message