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* >>> >> >> >
