Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Convert UTC timestamp to PST


Copy link to this message
-
Re: Convert UTC timestamp to PST
 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" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> 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 < [EMAIL PROTECTED] > 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
[EMAIL PROTECTED]
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB