You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Christopher Matta <cm...@mapr.com> on 2015/03/31 00:08:39 UTC
Convert UTC to specific timezone?
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
Re: Convert UTC to specific timezone?
Posted by Ted Dunning <te...@gmail.com>.
Ah... if you have datasets with other time zones, that is relatively easily
done. Just convert all of the times into time stamps and compare to your
hearts content.
If your data has an implied time-stamp, you can append a correct one and
use the z format specifier to parse the concatenated string.
On Tue, Mar 31, 2015 at 1:37 PM, Vince Gonzalez <vi...@gmail.com>
wrote:
> On Tuesday, March 31, 2015, Andries Engelbrecht <aengelbrecht@maprtech.com
> >
> wrote:
>
> > 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.
>
>
> When parsing a timestamp joda seems to want to parse the timestamp in the
> current time zone, which can be overridden. The timezone defaults to
> whatever java picks up when drill starts up, which can cause exceptions
> when "illegal instants" happen.
>
> I started at thread when I was getting "Illegal instant due to time zone
> offset transition" (which is also the subject line for the thread I
> started).
>
> It seems to me that the best way to ensure that Drill is really using UTC
> is to make sure you set user.timezone to UTC in drill-override.conf. It at
> least helps avoid the "illegal instant" exception I hit.
>
>
>
>
> >
> > 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 <cm...@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 <te...@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 <ba...@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
> > >>>>>
> > >>>>>
> > >>>>
> > >>>
> > >>>
> > >>
> >
> >
>
Convert UTC to specific timezone?
Posted by Vince Gonzalez <vi...@gmail.com>.
On Tuesday, March 31, 2015, Andries Engelbrecht <ae...@maprtech.com>
wrote:
> 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.
When parsing a timestamp joda seems to want to parse the timestamp in the
current time zone, which can be overridden. The timezone defaults to
whatever java picks up when drill starts up, which can cause exceptions
when "illegal instants" happen.
I started at thread when I was getting "Illegal instant due to time zone
offset transition" (which is also the subject line for the thread I
started).
It seems to me that the best way to ensure that Drill is really using UTC
is to make sure you set user.timezone to UTC in drill-override.conf. It at
least helps avoid the "illegal instant" exception I hit.
>
> 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 <cm...@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 <te...@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 <ba...@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
> >>>>>
> >>>>>
> >>>>
> >>>
> >>>
> >>
>
>
Re: Convert UTC to specific timezone?
Posted by Andries Engelbrecht <ae...@maprtech.com>.
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 <cm...@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 <te...@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 <ba...@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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
Re: Convert UTC to specific timezone?
Posted by Christopher Matta <cm...@mapr.com>.
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 <te...@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 <ba...@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
> > >>
> > >>
> > >
> >
> >
>
Re: Convert UTC to specific timezone?
Posted by Ted Dunning <te...@gmail.com>.
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 <ba...@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
> >>
> >>
> >
>
>
Re: Convert UTC to specific timezone?
Posted by Andries Engelbrecht <ae...@maprtech.com>.
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 <ba...@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
>>
>>
>
Re: Convert UTC to specific timezone?
Posted by Mehant Baid <ba...@gmail.com>.
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
>
>