I don’t think 4713 BC comes from the SQL standard. That is a Postgres thing.
I believe that the standard says you should support timestamp precision up to 9 (i.e. nanoseconds). 2 ^ 64 nanoseconds is 584 years. So, it’s not possible to cram all of the timestamp values we’d like into a 64 bit integer if you are going to support such a large time range. Julian > On Mar 14, 2017, at 11:29 AM, Boaz Ben-Zvi <bben-...@mapr.com> wrote: > > 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 > <prog...@mapr.com<mailto:prog...@mapr.com>> 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 > <par...@apache.org<mailto:par...@apache.org>> 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 > >
