hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Omernik <j...@omernik.com>
Subject Re: Timestamp, Epoch Time, Functions and other Frustrations
Date Sun, 06 Jan 2013 00:13:04 GMT
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 <john@omernik.com> 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 <grover.markgrover@gmail.com>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 <brad.cavanagh@gmail.com>
>> 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 <john@omernik.com> 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.
>> >
>> >
>>
>
>

Mime
View raw message