Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 07871E845 for ; Sun, 6 Jan 2013 00:13:52 +0000 (UTC) Received: (qmail 74814 invoked by uid 500); 6 Jan 2013 00:13:50 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 74746 invoked by uid 500); 6 Jan 2013 00:13:50 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 74736 invoked by uid 99); 6 Jan 2013 00:13:50 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Jan 2013 00:13:50 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of john@omernik.com designates 74.125.82.173 as permitted sender) Received: from [74.125.82.173] (HELO mail-we0-f173.google.com) (74.125.82.173) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Jan 2013 00:13:45 +0000 Received: by mail-we0-f173.google.com with SMTP id z2so8467650wey.4 for ; Sat, 05 Jan 2013 16:13:24 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type:x-gm-message-state; bh=OnEKJFMle5eM+E7EVzGu+rKq3mN94qCshqEORKRW9m8=; b=UCsobQsb0/VdD87FCG8k0CoC3yhSuCLjm83iUatsvs2delQhd/puT5oCHppPfGq7bN sS6anAuE3lQ1lSEsRS3Vijty+Hf3v33Dxu9MR7+tUfEUjT4FJoH1+oxPA9EEZ+oOPLit 8g16OuuVnvLBm6v+gOluqlccXM4Kn41I9VxIYbiTMvSewpVd9TIkQd5O9dSC0jO4mE3s t6jZFNSDLsW66afvyN3DDVSws6HmExVk665G+Cw1XCluUUrlYW2kmbq8w0VUo24zm9g/ zsGcT087Uwcyq5gLT/rk4RK2j9Dr3slzJNgmVzYgpQuhEYHeULrilaUn+CkyZSDe7fEl u5ig== Received: by 10.194.76.137 with SMTP id k9mr90129562wjw.29.1357431204282; Sat, 05 Jan 2013 16:13:24 -0800 (PST) MIME-Version: 1.0 Received: by 10.194.135.129 with HTTP; Sat, 5 Jan 2013 16:13:04 -0800 (PST) In-Reply-To: References: <1AE7ACA3-4D64-4DA7-8D04-B407479D1054@gmail.com> From: John Omernik Date: Sat, 5 Jan 2013 18:13:04 -0600 Message-ID: Subject: Re: Timestamp, Epoch Time, Functions and other Frustrations To: user@hive.apache.org Content-Type: multipart/alternative; boundary=047d7bfceb18bd00f604d2939420 X-Gm-Message-State: ALoCoQlq1o64bZM0EHlr/6NAiAJNwJvbpgtqEFtLw/Tp0MIqDCTq1p0q+SYtA/vFjU+9gsvzrzNW X-Virus-Checked: Checked by ClamAV on apache.org --047d7bfceb18bd00f604d2939420 Content-Type: text/plain; charset=ISO-8859-1 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 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 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 >> 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 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. >> > >> > >> > > --047d7bfceb18bd00f604d2939420 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Mark - I see your discussion with Mr. Harris here:


I agree that the r= esult of the from_unixtime() function would return the ts based on the syst= em time, but and struggling with the cast(int as timestamp) returning a val= ue affected by the system time. =A0This does not make sense, if we have a v= alue that is =A0an integer, it is timezone less, if we are casting the valu= e to a timezonel ess value (timestamp) then it should not be affected by an= y 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 hea= rtburn down the road. =A0I completely understand the from_unixtime() using = the timezone, but not the cast. =A0I think the difference is when a date is= is converted to a human readable form, then it is a acceptable, even norma= l to use the timezone of the system, whereas if the=A0conversion=A0is 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)

=A0I am open to seeing where I am looking at things wro= ng.=A0

On Fri, Jan 4, 2013 at 12:06 PM, J= ohn Omernik <john@omernik.com> wrote:
So I read that JIRA, and also found this lin= ked JIRA:


So I decided to try the * 1.0 work around.=A0

select=A0
starttime,=A0
from_unixtime(starttime) as unixtime, =A0
cast((starttime * 1.0) = =A0as timestamp) as castts,=A0
from_utc_timestamp(starttime * 1.0= , 'GMT') as fromtsgmt,
from_utc_timestamp(starttime * 1.0= , 'CST') asfromtscst=A0
from table

Hypothesis give=A0starttime=3D=A01= 356588013 (and based off the ep= och convertor website)

unixtime =3D 2012-12-27 00:= 00:13 # This is because unix time displays the time in the system time zone=
castts =3D 2012-12-27 06:00:13.0 =A0# This is because timestamp is a U= TC time, it should match the GMT time =
fromtsgmt =3D 2012-12-27 06:00:13.0 # This should be exac= tly what the TS is so it should be the same as the cast
fromtsCST =3D2012-12-27 00:00:13.0 # This should be the same (time bas= ed) result as from from_unixtime

Actual Results:

unixtime =3D2012-12-27 00:00:13 # 1 for 1 !
castts =3D 2012-12-27 00:00:13.0 # What? Why is this the same as unixtime?<= /div>
fromtsgmt =3D 2012-12-27 00:00:13.0 # What is THIS the same as un= ixtime?
fromtscst =3D 2012-12-26 18:00:13.0 # This is 6 hours beh= ind? Why did my epoch time get coverted to timestamp as if we added 6 to th= e hour?=A0

! =A0That makes NO sense, even ignoring the bug in the= =A0conversion=A0requiring a float, am I doing this wrong or is there a diff= erent bug in how this is approached?=A0




On Fri, Jan 4,= 2013 at 10:30 AM, Mark Grover <grover.markgrover@gmail.com&= gt; 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.cavanagh@gmail.com> wrote:<= br> > Try multiplying your values by 1000, then running the conversions. I b= et
> they expect milliseconds since the epoch instead of seconds.
>
> Brad.
>
>
> On 2013-01-04, at 8:03 AM, John Omernik <john@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 po= st
> 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 va= rious
> functions.
>
> Value =3D 1356588013
>
> Hive:
>
> from_unixtime(Value) =3D 2012-12-27 00:00:13 (Timezone CST on the syst= em time,
> so that works)
> cast(value as timestamp) =3D 1970-01-16 10:49:48.013
> cast(cast(value as int) as timestamp =3D 1970-01-16 10:49:48.013
> from_utc_timestamp(starttime, 'GMT') =3D 1970-01-16 10:49:48.0= 13
> from_utc_timestamp(starttime, 'CST') =3D 1970-01-16 04:49:48.0= 13
>
>
> 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 va= lid
> epoch time) into a GMT time basically, 2012-12-27 06:00:13 without jus= t
> 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? =A0 Why does it read 1970? =A0This is ver= y
> frustrating and should be more intuitive. =A0Please advise.
>
>


--047d7bfceb18bd00f604d2939420--