One more test: to_utc_timestamp(from_unixtime(value), 'CST') as to_from, provided the proper timestamp for me, however, I still had to provide the timezone which I should NOT have to do. I know that this data coming in is in epoch time, therefore I should be able to create a timezone without knowing a timezone or timezone offset.
On Fri, Jan 4, 2013 at 10: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. > > >
