Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Timestamp, Epoch Time, Functions and other Frustrations

Copy link to this message
Timestamp, Epoch Time, Functions and other Frustrations
Greetings all. I am getting frustrated with the documentation and lack of
intuitiveness in Hive relating to timestamps and was hoping I could post
here and get some clarification or other ideas.

I have a field that is a string, but is actually a 10 digit int
representation of epoch time, I am going to list out the results of various

Value = 1356588013


from_unixtime(Value) = 2012-12-27 00:00:13 (Timezone CST on the system
time, so that works)
cast(value as timestamp) = 1970-01-16 10:49:48.013
cast(cast(value as int) as timestamp = 1970-01-16 10:49:48.013
from_utc_timestamp(starttime, 'GMT') = 1970-01-16 10:49:48.013
from_utc_timestamp(starttime, 'CST') = 1970-01-16 04:49:48.013
Epoch Converter - http://www.epochconverter.com/

Thu, 27 Dec 2012 06:00:13 GMT - GMT Representation of the time
Thu Dec 27 2012 00:00:13 GMT-6 - My Timezone representation

Ok Given all of these representations... how do I get the Value ( a valid
epoch time) into a GMT time basically, 2012-12-27 06:00:13 without just
doing math. (Math is error prone on system as we move across timezone). Why
doesn't the casting of the value to timestamp or even the casting of the
int cast of the time stamp work?   Why does it read 1970?  This is very
frustrating and should be more intuitive.  Please advise.
John Omernik 2013-01-04, 16:08
Brad Cavanagh 2013-01-04, 16:25
Mark Grover 2013-01-04, 16:30
John Omernik 2013-01-04, 18:06
John Omernik 2013-01-06, 00:13