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 <[email protected]> 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 > <[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. >> > >> > >> > >
