Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Timestamp, Epoch Time, Functions and other Frustrations


Copy link to this message
-
Re: Timestamp, Epoch Time, Functions and other Frustrations
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
> >
> >
> > 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.
> >
> >
>