You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by John Omernik <jo...@omernik.com> on 2013/01/04 17:03:48 UTC
Timestamp, Epoch Time, Functions and other Frustrations
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.
Re: Timestamp, Epoch Time, Functions and other Frustrations
Posted by John Omernik <jo...@omernik.com>.
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 <jo...@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.
>
>
>
Re: Timestamp, Epoch Time, Functions and other Frustrations
Posted by John Omernik <jo...@omernik.com>.
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 <jo...@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 <gr...@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 <br...@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 <jo...@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.
>> >
>> >
>>
>
>
Re: Timestamp, Epoch Time, Functions and other Frustrations
Posted by John Omernik <jo...@omernik.com>.
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 <gr...@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 <br...@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 <jo...@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.
> >
> >
>
Re: Timestamp, Epoch Time, Functions and other Frustrations
Posted by Mark Grover <gr...@gmail.com>.
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 <br...@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 <jo...@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.
>
>
Re: Timestamp, Epoch Time, Functions and other Frustrations
Posted by Brad Cavanagh <br...@gmail.com>.
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 <jo...@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.
>
>