Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-262

Implement support for DATETIME and TIMESTAMP types


    • Type: Task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.4.0
    • Component/s: None
    • Labels:


      Many functions and operators in Drill need to handle DATETIME and TIMESTAMP types. This may require two formats; the most important of which is an expanded format for use during processing:

              struct pg_tm
                int     tm_sec;
                int     tm_min;
                int     tm_hour;
                int     tm_mday;
                int     tm_mon;     /* origin 0, not 1 */
                int     tm_year;    /* relative to 1900 */
                int     tm_wday;
                int     tm_yday;
                int     tm_isdst;
                long int  tm_gmtoff;
                const char *tm_zone;

      Note that:

      • Fractions of seconds are stored in an auxiliary 32-bit integer (thus internal time functions usually take both pg_tm and fsec_t arguments).
      • Resolution is guaranteed to the microsecond, with a date range of 4713 BC - 294276 AD.
      • MySQL stores datetime values as simple unix time format (second resolution, 1970 - 2038 date range)

      In Postgres, the TIMESTAMP type is encoded into 64 bits for storage as follows:

      • The date portion is packed in the upper 28 bits as the delta of years, months and days since postgres epoch (1/1/2000).
      • The time portion is packed in the lower 36 bits as hours, minutes, seconds and fsecs (fractions of a second).
      • The specific encoding logic can be found in src/backend/utils/adt/timestamp.c in tm2timestamp(), date2j() and time2t().




            • Assignee:
              benjamin.becker Ben Becker
            • Votes:
              0 Vote for this issue
              5 Start watching this issue


              • Created: