drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andries Engelbrecht <aengelbre...@maprtech.com>
Subject Re: Convert UTC to specific timezone?
Date Tue, 31 Mar 2015 15:09:31 GMT
TMZ functionality will be good, especially if it can be specified in a query and also set at
session level.

Another question is if Drill will assume all date/time data is UTC unless specified differently?
I believe this to be the case.

Date_add, Date_sub seems to only support days.

You can also use date_part to pull out the specifics. So basically have to do a bit of work
to extract, apply some logics and reconstruct the timestamp.

Any other suggestions will be welcome.

—Andries



On Mar 31, 2015, at 5:57 AM, Christopher Matta <cmatta@mapr.com> wrote:

> Ted's correct, it would be nice to be able to convert the UTC datetime
> column to whichever timezone I'm interested in, say 'America/New_York', as
> a timestamp so I can compare datasets that don't have UTC timestamps with
> those that do. Is this in the roadmap?
> 
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
> 
> On Tue, Mar 31, 2015 at 3:11 AM, Ted Dunning <ted.dunning@gmail.com> wrote:
> 
>> The original poster wasn't very clear.  What they said could mean what
>> Andries provided (which is to determine which timezone that data refers
>> to).
>> 
>> The way that I read the question was that they wanted to translate times to
>> be represented as the string formatted version of the same time in a
>> different timezone.
>> 
>> 
>> 
>> On Mon, Mar 30, 2015 at 6:02 PM, Andries Engelbrecht <
>> aengelbrecht@maprtech.com> wrote:
>> 
>>> Here is the syntax for I could figure out for timezone.
>>> 
>>> select to_timestamp('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s
>>> z') as Original, to_char(to_timestamp('2015-03-30 20:49:59.0 UTC',
>>> 'YYYY-MM-dd HH:mm:ss.s z'), 'z') as New_TZ from sys.version;
>>> 
>>> +------------+------------+
>>> |  Original  |   New_TZ   |
>>> +------------+------------+
>>> | 2015-03-30 20:49:00.0 | UTC        |
>>> +------------+------------+
>>> 
>>> Using ‘Z’ will provide offset from UTC as opposed to the 3 letter
>> timezone
>>> code.
>>> 
>>> —Andries
>>> 
>>> 
>>> On Mar 30, 2015, at 5:23 PM, Mehant Baid <baid.mehant@gmail.com> wrote:
>>> 
>>>> Currently in Drill there isn't support for 'timestamp with time zone'
>>> data type, all the timestamp/date information is stored in UTC and no
>>> timezone information is maintained, so AFAIK there isn't really a way to
>>> convert dates/timestamp to a specific timezone. However if your input
>> data
>>> contains timezone information Drill should be able to parse the input and
>>> convert it to UTC (if you use the 'z' format specifier).
>>>> 
>>>> Thanks
>>>> Mehant
>>>> 
>>>> On 3/30/15 3:08 PM, Christopher Matta wrote:
>>>>> I have a column that’s UTC, how can I convert this column to a
>> specific
>>>>> timezone?
>>>>> 
>>>>> 
>>>>>> select to_timestamp(columns[13], 'YYYY-MM-dd HH:mm:ss') as
>>> `datetime_utc` from `weather/2012` limit 10;
>>>>> +--------------+
>>>>> | datetime_utc |
>>>>> +--------------+
>>>>> | 2012-02-01 05:54:00.0 |
>>>>> | 2012-02-01 06:54:00.0 |
>>>>> | 2012-02-01 07:54:00.0 |
>>>>> | 2012-02-01 08:54:00.0 |
>>>>> 
>>>>> Chris Matta
>>>>> cmatta@mapr.com
>>>>> 215-701-3146
>>>>> ​
>>>>> 
>>>> 
>>> 
>>> 
>> 


Mime
View raw message