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
