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 :-)).
Thanks,
— Boaz
On Mar 13, 2017, at 3:46 PM, Paul Rogers
<[email protected]<mailto:[email protected]>> wrote:
Thanks Parth!
The date and time definitions are the “classic” ones, but conflict with the
Drill documentation:
http://drill.apache.org/docs/supported-data-types/
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: HH:mm:ss
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...
Thanks,
- Paul
On Mar 13, 2017, at 2:44 PM, Parth Chandra
<[email protected]<mailto:[email protected]>> wrote:
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
TimeZones)
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.
HTH
Parth
