-Re: Timestamp, Epoch Time, Functions and other Frustrations
John Omernik 2013-01-04, 18:06
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
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
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 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
> > 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
> > 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).
> > doesn't the casting of the value to timestamp or even the casting of the
> > cast of the time stamp work? Why does it read 1970? This is very
> > frustrating and should be more intuitive. Please advise.