drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boaz Ben-Zvi <bben-...@mapr.com>
Subject Re: Drill date & time types encoding
Date Tue, 14 Mar 2017 18:29:07 GMT
Thanks for the detailed research, Paul,

  INTERVAL by the (SQL-99 ?) standard should be either a SECOND-DAY interval or a MONTH-YEAR
interval. This is a result of the inconsistent “number of days in a month”.

DATE expressed in days, starting at 4713-BC sound like the standard as well (at least same
as Postgres). (should be implemented as 4 byte)

TIME is only within a 24 hour period, so why should it care about 2001 ?  Probably the documentation
should be fixed. (should be implemented as 4 byte)

TIMESTAMP should be an 8 byte type (maybe starting at 4713-BC as well, to match DATE (and
Postgres :-)).


           — Boaz

On Mar 13, 2017, at 3:46 PM, Paul Rogers <progers@mapr.com<mailto:progers@mapr.com>>

Thanks Parth!

The date and time definitions are the “classic” ones, but conflict with the Drill documentation:


DATE Years, months, and days in YYYY-MM-DD format since 4713 BC

TIME 24-hour based time before or after January 1, 2001 in hours, minutes, seconds format:

Which is correct?

If the documentation is wrong, we can file a JIRA to correct it. (It may not even be wrong,
since one can convert from one to the other easily, it may just be misleading…)

Also note that, according to C++, DATE and TIME and TIMESTAMP are exactly the same, but the
TIME as as 32-bit number, could only hold about 2 years due to limited range.

Also, according to SQL, DATE has no time zone, it is just a date. That is, 2016-03-13 is the
same date in PST or GMT. If DATE were seconds since the UTC epoch, dates would be different
in different time zones. So, I assume we use the Unix epoch, but without an implied UTC time
zone as is usual for Linux and Windows timestamps?

How does a TIMESTAMP differ from a DATE? Perhaps a TIMESTAMP is based on the epoch UTC while
DATE has no implied time zone?

Again, the documentation differs:

INTERVAL (Internally, INTERVAL is represented as INTERVALDAY or INTERVALYEAR.) A day-time
or year-month interval

TIMESTAMP JDBC timestamp in year, month, date hour, minute, second, and optional milliseconds
format: yyyy-MM-dd HH:mm:ss.SSS

So, sounds like we have an INTERVALDAY and INTERVAL year, but do we or do we not have an INTERVAL?

If anyone knows, please let me know, else I need to do some poking around...


- Paul

On Mar 13, 2017, at 2:44 PM, Parth Chandra <parthc@apache.org<mailto:parthc@apache.org>>

Paul asked this and I'm posting here so someone who knows better can
correct me if I'm wrong ( This is from my notes when I was young)

DATE : Int64 : Milliseconds from Unix Epoch : 1/1/1970 00:00:00
TIME : Int32 : Milliseconds from midnight on 1/1/1970
TimeStampTZ : Int64 + Int32 : (Milliseconds from epoch + Index into list of
TimeStamp : Int64 : Milliseconds from epoch
Interval : Int32 + Int32 + Int32 : Month + Days + Milliseconds
Interval Day : Int32 + Int32 : Days + Milliseconds
Interval Year : Int32 : Month

A slightly readable version of these can be found in the C++ client :).
$drill_src/contrib/native/client/src/include/drill/recordbatch.hpp which
has a bunch of 'Holder' structs for the date-time types.



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