drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vince Gonzalez <vince.gonza...@gmail.com>
Subject Convert UTC to specific timezone?
Date Tue, 31 Mar 2015 20:37:09 GMT
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 <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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message