Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id A8C81200B90 for ; Sun, 25 Sep 2016 08:41:46 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id A7448160ACE; Sun, 25 Sep 2016 06:41:46 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 73E43160AC7 for ; Sun, 25 Sep 2016 08:41:45 +0200 (CEST) Received: (qmail 94652 invoked by uid 500); 25 Sep 2016 06:41:44 -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 94641 invoked by uid 99); 25 Sep 2016 06:41:43 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 25 Sep 2016 06:41:43 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 6FB5FC1621 for ; Sun, 25 Sep 2016 06:41:43 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3.881 X-Spam-Level: *** X-Spam-Status: No, score=3.881 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, KAM_BADIPHTTP=2, NORMAL_HTTP_TO_IP=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001, WEIRD_PORT=0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx2-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id k5eeDRzx_A-n for ; Sun, 25 Sep 2016 06:41:41 +0000 (UTC) Received: from mail-oi0-f49.google.com (mail-oi0-f49.google.com [209.85.218.49]) by mx2-lw-eu.apache.org (ASF Mail Server at mx2-lw-eu.apache.org) with ESMTPS id 230EB5F1E3 for ; Sun, 25 Sep 2016 06:41:40 +0000 (UTC) Received: by mail-oi0-f49.google.com with SMTP id r126so176609309oib.0 for ; Sat, 24 Sep 2016 23:41:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=TziHR5qIrGM/z199IYruYvMyR4z/DZeahgNGjnWSzxs=; b=QRvpvY2mSM4f/AA3Spj81Ux0dQVdp4QpglaVm2Fu+soO2ouFIGHwvwkUrH0RogcPBL 9fTVMOz+VNl6XNXhoaYOnm0lIFeBVcqOP9TKnVXhPm3ElnW3CmO7Vk/MJvYalGd7sv3r 3ak8PMz+XZRKMDD7lxNk2M467WJqy/HnN7wyhgynFLl+1myY2nPX8HMHB5MGmnjqWzvg AI6WhbMOtyhlQlfeML6bHpQ8/bSIzVWEoGZ0e7SeSaeBgiR8x0KJF7musQJ22VjFJt0P f0WQ5gjbWcMrTJZukNfv5vU24FQQipos2PffyiOB3AAMrrb+/kkHYPz3gSyVWQRSTrDo PKFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=TziHR5qIrGM/z199IYruYvMyR4z/DZeahgNGjnWSzxs=; b=IxgK5q8lpnepEw4VLQMtrDcLNkjjjWIKO1jynzU/htJloGAIDbiTWoociyMQ+JgBX2 h9NjKZmG7oVWxddfGPoxLKYj1AoGpVzbgAeRwLqKhS5nbY8A0dzkXcsdWpMgOnpM/HSQ dst/R1hkxjAk1yPEVK+3nC0DWCfGWZHPdmxQcihZxjTgsic+J/e3X9tHDsJh16auE3B6 dm3Ss2Iw3FJlgjIyFEUJO+Zx4ygKw1+l/mCV8L94ZOMK9tjtRj7BKZl2aN1qKbe9/1R6 rBZcQH4DKGB6sztww4Ly0GwSzzSqXklAk1ca2ijzNALhqOnbFe4dipTg48yTpkOsxMCy BavQ== X-Gm-Message-State: AE9vXwNWPmU9yqrF4r7FzGtRYJQBK+bAn6IjdTeYDZCxAShrf6u6Lw0YOugWhMGw4WGEuM0GD0mctNCDg51iXA== X-Received: by 10.202.169.202 with SMTP id s193mr19600328oie.21.1474785698725; Sat, 24 Sep 2016 23:41:38 -0700 (PDT) MIME-Version: 1.0 Received: by 10.202.188.84 with HTTP; Sat, 24 Sep 2016 23:41:38 -0700 (PDT) In-Reply-To: References: From: Manish R Date: Sun, 25 Sep 2016 12:11:38 +0530 Message-ID: Subject: Re: iso 8601 to utc with timezone conversion To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a113ce69cb1432a053d4f4d84 archived-at: Sun, 25 Sep 2016 06:41:46 -0000 --001a113ce69cb1432a053d4f4d84 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Guys, I tried below query but it looks like the conversion is somehow not correct. The logs are from AWS ELB in Singapore region. Can you guys please check and let me know what am I missing? hive> SELECT s.request_date, a.tz, FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.request_date, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'") * 1000, a.tz) from small_table s JOIN aid_tz a on (a.aid =3D s.aid) limit 3; *2016-09-15T23:04:18.013208Z Australia/Sydney 2016-09-16 09:04:31* *2016-09-15T23:04:17.397250Z Australia/Sydney 2016-09-16 09:10:54* *2016-09-15T23:04:18.972533Z UTC 2016-09-15 23:20:30* On Fri, Sep 23, 2016 at 1:24 PM, Manish R wrote: > Yes Sekine I am talking about AWS ELB logs in Mumbai region. Let me try > implementing what Andres suggested and I also in a verge of implementing > some other solution as well. I will let you all know once any of the > solution works. > On Sep 23, 2016 1:11 PM, "S=C3=A9kine Coulibaly" w= rote: > >> Manish, >> >> UTC is not a format (but, ISO 8601 is). >> Consider UTC as +0000 at the end of a ISO 8601 time. >> >> Eg: >> 2016-01-01T*23:45:22.943762*+0000 >> >> is stricylt equivalent to : >> 2016-01-01T*23:45:22.943762Z* >> >> *and is also strictly equivalent to the same time expressed in another >> timezone such as Europe/Pa**ris (CEST) :* >> 2016-01-02T01*:45:22.943762+0200* >> >> The documentation you cite says that the format is a ISO 8601, but >> doesn't specify the timezone used in the format. I guess you are using A= WS >> Load Balancer logs. Please find an example of mine below : >> >> 2016-06-15T14:03:00.784203Z awseb-e-9-xxxxxxxxxx 194.154.216.89:58603 1.= 2.3.4:80 0.000041 0.426008 0.000047 200 200 163 168 "POST https://whatever.= domain.org:443/oauth/token HTTP/1.1" "UA" AES256-SHA256 TLSv1.2 >> >> As you can see, the timestamp is ISO8601 compliant, and does specify Z, = so as Andres explained, this is a UTC time. >> >> >> Regards >> >> Sekine >> >> >> 2016-09-23 5:48 GMT+02:00 Manish R : >> >>> Hi Andres, >>> >>> No that is not in UTC format. Plz see the description of that field >>> below. so if timezone of table2 is Europe/Amsterdam then we have to con= vert >>> the request_date of table1 in UTC Europe/Amsterdam timezone ( for examp= le >>> 2016-09-18 23:30:52). We have a lot of timezone entries in table2 and I >>> wonder how am I going to convert all the request_date field according t= o >>> timezone field. Do I have to maintains separate table for that? >>> >>> timestamp >>> >>> The time when the load balancer received the request from the client, i= n >>> ISO 8601 format. >>> >>> On Fri, Sep 23, 2016 at 1:26 AM, Andres Koitm=C3=A4e < >>> andres.koitmae@gmail.com> wrote: >>> >>>> Hi! >>>> >>>> It seems that in Table1 you already have request_date in UTC format. *= Z >>>> *at the end of the timezone is the zone designator for the zero UTC >>>> offset. >>>> >>>> Now all you have to do is to use standard Hive functions which you can >>>> find from Hive wiki https://cwiki.apache.org/ >>>> confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF >>>> -TypeConversionFunctions >>>> >>>> Use from_utc_timestamp to convert request_date to timestamp to >>>> timezone specified in Table 2 (join two tables using aid column) >>>> >>>> Regards, >>>> >>>> Andres Koitm=C3=A4e >>>> >>>> On 22 September 2016 at 20:05, Manish R >>> > wrote: >>>> >>>>> Hi Guys, >>>>> >>>>> There is a scenario here that I am trying to implement >>>>> >>>>> I have a table say table1 which contains aid and request_date in ISO >>>>> 8601 format. I have one more table say table2 which contains aid and >>>>> timezone details. Now I want to convert request_date from table1 to U= TC and >>>>> apply the timezone that is in table2 format for that corresponding ai= d. >>>>> >>>>> Table 1 example data >>>>> *2016-09-15T23:45:22.943762Z abs123* >>>>> *2016-09-16T22:48:12.943762Z erty456* >>>>> >>>>> Table 2 example data >>>>> *abs123 Asia/Kolkata* >>>>> *erty456 Europe/Amsterdam* >>>>> >>>> >>>> >>> >> --001a113ce69cb1432a053d4f4d84 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Guys,

I tried below query but it looks = like the conversion is somehow not correct. The logs are from AWS ELB in Si= ngapore region. Can you guys please check and let me know what am I missing= ?

hive> SELECT s.request_date, a.tz, FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.request_date, &q= uot;yyyy-MM-dd'T'hh:mm:ss.SSS'Z'") * 1000, a.tz) from small_table s JOIN aid_tz a on (a.aid =3D s.aid= ) limit 3;

2016-09-15T23:04:18.013208Z A= ustralia/Sydney 2016-09-16 09:04:31
2016-09-15T23:04:17.39= 7250Z Australia/Sydney 2016-09-16 09:10:54
2016-09-15T23:0= 4:18.972533Z UTC 2016-09-15 23:20:30


On Fri, Sep 23, 2016= at 1:24 PM, Manish R <linuxtricksfordevops@gmail.com> wrote:
Yes Sekine I am talking about AWS ELB logs in Mumbai region. L= et me try implementing what Andres suggested and I also in a verge of imple= menting some other solution=C2=A0 as well. I will let you all know once any= of the solution works.

On Sep 23, 2016 1:11 PM, "S=C3=A9kine Couli= baly" <sc= oulibaly@gmail.com> wrote:
Manish,

UTC is not a format (but, ISO 8601 is).
Consider UTC as +0000 a= t the end of a ISO 8601 time.

Eg:
2016-0= 1-01T23:45:22.943762+0000

= is stricylt equivalent to :
2016-01-01T23:45:22.94= 3762Z

an= d is also strictly equivalent to the same time expressed in another timezon= e such as Europe/Paris (CEST) :
2016-01-02= T01:45:22.943762+0200

= The documentation you cite says that the format is a ISO 8601, but doesn= 9;t specify the timezone used in the format. I guess you are using AWS Load= Balancer logs. Please find an example of mine below :
2016-06-15T=
14:03:00.784203Z awseb-e-9-xxxxxxxxxx 194.154.216.89:58603 1.2.3.4:80 0.000041 0.426008 0.000047 200 200 163 16=
8 "POST https://whatever.domain.org:443/oauth/token HTTP/1.1&q=
uot; "UA" AES256-SHA256 TLSv1.2
As you can see, the timestamp=
 is ISO8601 compliant, and does specify Z, so as Andres explained, this is =
a UTC time.

Regards
Sekine

2016-09-23 5:48 GMT+02:00 Manis= h R <linuxtricksfordevops@gmail.com>:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
Hi Andres= ,

No that is not in UTC format. Plz see the description = of that field below. so if timezone of table2 is Europe/Amsterdam then we h= ave to convert the request_date of table1 in UTC=C2=A0Europe/Amsterdam time= zone ( for example 2016-09-18 23:30:52). We have a lot of timezone entries = in table2 and I wonder how am I going to convert all the request_date field= according to timezone field. Do I have to maintains separate table for tha= t?

t= imestamp

The time when t= he load balancer received the request from the client, in ISO 8601 format.<= /p>


On Fri, Sep 23, 2016 at 1:26 AM, An= dres Koitm=C3=A4e <andres.koitmae@gmail.com> wrote:
Hi!
It seems that in Table1 you already have request_date in U= TC format.=C2=A0Z at the end of the timezone=C2=A0is the zone designat= or for the zero UTC offset.

Now all yo= u have to do is to use standard Hive functions which you can find from Hive= wiki=C2=A0https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-TypeConversionFunctions

Use=C2=A0fro= m_utc_timestamp to convert request_date to timestamp to timezone specified = in Table 2 (join two tables using aid column)

Rega= rds,

Andres Koitm=C3=A4e



--001a113ce69cb1432a053d4f4d84--