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
+
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
Copy link to this message
-
Re: Timestamp, Epoch Time, Functions and other Frustrations
John Omernik 2013-01-06, 00:13
Mark - I see your discussion with Mr. Harris here:

https://issues.apache.org/jira/browse/HIVE-3822

I agree that the result of the from_unixtime() function would return the ts
based on the system time, but and struggling with the cast(int as
timestamp) returning a value affected by the system time.  This does not
make sense, if we have a value that is  an integer, it is timezone less, if
we are casting the value to a timezonel ess value (timestamp) then it
should not be affected by any system timezone, this is is counter intuitive
and requires a user to set work arounds (setting the timezone of a JVM etc)
that may cause further heartburn down the road.  I completely understand
the from_unixtime() using the timezone, but not the cast.  I think the
difference is when a date is is converted to a human readable form, then it
is a acceptable, even normal to use the timezone of the system, whereas if
the conversion is to a type such as timestamp which is by design
timzoneless, we should not apply a timezone to it. (unless specified
through the helper functions)

 I am open to seeing where I am looking at things wrong.

On Fri, Jan 4, 2013 at 12:06 PM, John Omernik <[EMAIL PROTECTED]> wrote:

> So I read that JIRA, and also found this linked JIRA:
>
> https://issues.apache.org/jira/browse/HIVE-3454
>
> So I decided to try the * 1.0 work around.
>
> select
> starttime,
> from_unixtime(starttime) as unixtime,
>  cast((starttime * 1.0)  as timestamp) as castts,
> from_utc_timestamp(starttime * 1.0, 'GMT') as fromtsgmt,
> from_utc_timestamp(starttime * 1.0, 'CST') asfromtscst
> from table
>
> Hypothesis give starttime= 1356588013 (and based off the epoch convertor
> website)
>
> unixtime = 2012-12-27 00:00:13 # This is because unix time displays the
> time in the system time zone
> castts = 2012-12-27 06:00:13.0  # This is because timestamp is a UTC time,
> it should match the GMT time
> fromtsgmt = 2012-12-27 06:00:13.0 # This should be exactly what the TS is
> so it should be the same as the cast
> fromtsCST =2012-12-27 00:00:13.0 # This should be the same (time based)
> result as from from_unixtime
>
> Actual Results:
>
> unixtime =2012-12-27 00:00:13 # 1 for 1 !
> castts = 2012-12-27 00:00:13.0 # What? Why is this the same as unixtime?
> fromtsgmt = 2012-12-27 00:00:13.0 # What is THIS the same as unixtime?
> fromtscst = 2012-12-26 18:00:13.0 # This is 6 hours behind? Why did my
> epoch time get coverted to timestamp as if we added 6 to the hour?
>
> !  That makes NO sense, even ignoring the bug in the conversion requiring
> a float, am I doing this wrong or is there a different bug in how this is
> approached?
>
>
>
>
>
> On Fri, Jan 4, 2013 at 10:30 AM, Mark Grover <[EMAIL PROTECTED]>wrote:
>
>> Brad is correct, there is a JIRA about this already:
>> https://issues.apache.org/jira/browse/HIVE-3822
>>
>> Sorry for the inconvenience.
>>
>> Mark
>>
>> On Fri, Jan 4, 2013 at 8:25 AM, Brad Cavanagh <[EMAIL PROTECTED]>
>> wrote:
>> > Try multiplying your values by 1000, then running the conversions. I bet
>> > they expect milliseconds since the epoch instead of seconds.
>> >
>> > Brad.
>> >
>> >
>> > On 2013-01-04, at 8: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