[DRILL-262] Implement support for DATETIME and TIMESTAMP types

• Type: Task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.4.0
• Assignee: Unassigned
• Reporter: Ben Becker
• Created: 10/Oct/13 18:58
• Updated: 15/Sep/15 00:00
• Resolved: 29/May/14 15:44

Description

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().