Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB