hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sékine Coulibaly <scoulib...@gmail.com>
Subject Re: iso 8601 to utc with timezone conversion
Date Fri, 23 Sep 2016 07:41:06 GMT
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 <linuxtricksfordevops@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.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äe
>>
>> On 22 September 2016 at 20:05, Manish R <linuxtricksfordevops@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*
>>>
>>
>>
>

Mime
View raw message