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 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.
> >
> >
>
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