-Re: Timestamp, Epoch Time, Functions and other Frustrations
John Omernik 2013-01-06, 00:13
Mark - I see your discussion with Mr. Harris here:
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:
> So I decided to try the * 1.0 work around.
> 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
> 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
> On Fri, Jan 4, 2013 at 10:30 AM, Mark Grover <[EMAIL PROTECTED]>wrote:
>> Brad is correct, there is a JIRA about this already:
>> Sorry for the inconvenience.
>> On Fri, Jan 4, 2013 at 8:25 AM, Brad Cavanagh <[EMAIL PROTECTED]>
>> > 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
>> > 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
>> > functions.
>> > Value = 1356588013
>> > Hive:
>> > from_unixtime(Value) = 2012-12-27 00:00:13 (Timezone CST on the system
>> > 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