drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hao Zhu <h...@maprtech.com>
Subject Re: Understanding Drill's timestamp and timezone
Date Tue, 12 May 2015 17:44:04 GMT
People who are familiar with postgreSQL may feel the confusion.
One reason is postgreSQL has data type "timestamp with time zone" and their
to_timestamp's return value is "timestamp with time zone".
It can append its timezone to the data instead of treating it as UTC.
This is a behavior difference.

postgres=# select to_timestamp('2012-02-04 05:00:00','YYYY-MM-DD hh:mi:ss'
);
      to_timestamp
------------------------
 2012-02-04 05:00:00+00
(1 row)

postgres=# show timezone;
 TimeZone
----------
 UTC
(1 row)

postgres=# set timezone=-5;
SET
postgres=# show timezone;
 TimeZone
-----------
 -05:00:00
(1 row)

postgres=# select to_timestamp('2012-02-04 05:00:00','YYYY-MM-DD hh:mi:ss'
);
      to_timestamp
------------------------
 2012-02-04 05:00:00-05
(1 row)


Thanks,
Hao


On Tue, May 12, 2015 at 9:53 AM, Kristine Hahn <khahn@maprtech.com> wrote:

> Thanks, yes it is confusing. There's some documentation  on
> http://drill.apache.org/docs/data-type-conversion/#time-zone-limitation
> about the limitation and conversion functions, but there's lots of room for
> improvement.
>
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
>
>
> On Mon, May 11, 2015 at 10:13 PM, Adam Gilmore <dragoncurve@gmail.com>
> wrote:
>
> > I must say - this is really confusing and seems to be undocumented.
> >
> > I think if Drill is going to not support a timestamp with timezone in the
> > near future, it should deal with ALL date/times as UTC, or at the very
> > least provide functions to convert between the two where applicable.
> >
> > For example, the new extended JSON support ($date) will parse a date such
> > as "2015-01-01T00:22:00Z" and convert it to the local time.  So if you
> were
> > to (as we are) create a Parquet table from JSON (with extended JSON),
> then
> > all the dates in the Parquet file will be created as local time
> > (incorrectly).
> >
> > On Fri, May 8, 2015 at 12:14 PM, Hao Zhu <hzhu@maprtech.com> wrote:
> >
> > > Hi Team,
> > >
> > > Recently spent some time to test the Drill's timestamp behavior, so
> > sharing
> > > the article "Understanding Drill's timestamp and timezone
> > > <
> http://www.openkb.info/2015/05/understanding-drills-timestamp-and.html
> > >".
> > >
> > > This article tests the behaviors under different Drill's timezones and
> > > source data's timezones.
> > >
> > > Thanks,
> > > Hao
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message