Affects Version/s: None
Fix Version/s: 0.4.0
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:
- 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().