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.i...@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.geh...@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 sa...@stumbleupon.com
