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

Implement support for DATETIME and TIMESTAMP types

    Details

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

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

        Activity

        Hide
        khfaraaz Khurram Faraaz added a comment -

        Drill supports DATE, TIME and TIMESTAMP datatypes, verified on commit ID: b525692e, on master.

        Show
        khfaraaz Khurram Faraaz added a comment - Drill supports DATE, TIME and TIMESTAMP datatypes, verified on commit ID: b525692e, on master.
        Hide
        julianhyde Julian Hyde added a comment -

        At the user level, I'd recommend that Drill supports DATE, TIME and TIMESTAMP. Later it can support TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE types.

        How these are represented internally is much less important.

        Show
        julianhyde Julian Hyde added a comment - At the user level, I'd recommend that Drill supports DATE, TIME and TIMESTAMP. Later it can support TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE types. How these are represented internally is much less important.

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development