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 <grover.markgro...@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 <brad.cavan...@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 <j...@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. > > > > >
