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ékine Coulibaly" <scoulib...@gmail.com> wrote:
> 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 AWS 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 <linuxtricksfordev...@gmail.com>: > >> 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 convert >> the request_date of table1 in UTC Europe/Amsterdam timezone ( 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 that? >> >> timestamp >> >> The time when the load balancer received the request from the client, in >> ISO 8601 format. >> >> On Fri, Sep 23, 2016 at 1:26 AM, Andres Koitmäe <andres.koit...@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äe >>> >>> On 22 September 2016 at 20:05, Manish R <linuxtricksfordev...@gmail.com> >>> 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 UTC and >>>> apply the timezone that is in table2 format for that corresponding aid. >>>> >>>> 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* >>>> >>> >>> >> >