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


+
John Omernik 2013-01-04, 16:03
Copy link to this message
-
Re: Timestamp, Epoch Time, Functions and other Frustrations
John Omernik 2013-01-04, 16:08
One more test:

to_utc_timestamp(from_unixtime(value), 'CST') as to_from, provided the
proper timestamp for me, however, I still had to provide the timezone which
I should NOT have to do. I know that this data coming in is in epoch time,
therefore I should be able to create a timezone without knowing a timezone
or timezone offset.

On Fri, Jan 4, 2013 at 10:03 AM, John Omernik <[EMAIL PROTECTED]> wrote:

> 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
> functions.
>
> Value = 1356588013
>
> Hive:
>
> 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.
>
>
>
+
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