Datetimes

arrays
foundation
datetimes
Datetime values work well as attributes or array domains within TileDB.

TileDB supports datetime values for attributes and array domains. The representation used by TileDB follows the design of NumPy’s np.datetime64 datatype.

Values for the datetime types are internally stored and manipulated as int64 values. From the perspective of core TileDB internally, the datetime datatypes are simply aliases for TILEDB_INT64.

The meaning of an integral datetime value depends on three things:

  1. A reference date. TileDB fixes this to the UNIX epoch time (1970-01-01 at 12:00 AM UTC). This is not currently configurable.

  2. A unit of time. For example: day, month, hour, or nanosecond.

  3. An integer value. This the integer number of time units relative to the reference date.

For example, a value of 10 for the type TILEDB_DATETIME_DAY refers to 12:00 am on 1970-01-10. A value of -18 for the type TILEDB_DATETIME_HR refers to 6:00 am on 1969-12-31, or 1969-12-31T06:00Z in ISO8601 format.

This means that each date unit of datetime is capable of representing a different range of dates at different resolutions. The following table (values taken from the NumPy np.datetime64 documentation) summarizes each date unit’s relative and absolute range:

Datatype Time span (relative) Time span (absolute)
"datetime64[Y]" +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
"datetime64[M]" +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
"datetime64[W]" +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
"datetime64[D]" +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
"datetime64[h]" +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
"datetime64[m]" +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
"datetime64[s]" +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
"datetime64[ms]" +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
"datetime64[us]" +/- 2.9e5 years [290301 BC, 294241 AD]
"datetime64[ns]" +/- 292 years [1678 AD, 2262 AD]
"datetime64[ps]" +/- 106 days [1969 AD, 1970 AD]
"datetime64[fs]" +/- 2.6 hours [1969 AD, 1970 AD]
"datetime64[as]" +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
"DATETIME_YEAR" +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
"DATETIME_MONTH" +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
"DATETIME_WEEK" +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
"DATETIME_DAY" +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
"DATETIME_HR" +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
"DATETIME_MIN" +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
"DATETIME_SEC" +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
"DATETIME_MS" +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
"DATETIME_US" +/- 2.9e5 years [290301 BC, 294241 AD]
"DATETIME_NS" +/- 292 years [1678 AD, 2262 AD]
"DATETIME_PS" +/- 106 days [1969 AD, 1970 AD]
"DATETIME_FS" +/- 2.6 hours [1969 AD, 1970 AD]
"DATETIME_AS" +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
TILEDB_DATETIME_YEAR +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
TILEDB_DATETIME_MONTH +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
TILEDB_DATETIME_WEEK +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
TILEDB_DATETIME_DAY +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
TILEDB_DATETIME_HR +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
TILEDB_DATETIME_MIN +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
TILEDB_DATETIME_SEC +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
TILEDB_DATETIME_MS +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
TILEDB_DATETIME_US +/- 2.9e5 years [290301 BC, 294241 AD]
TILEDB_DATETIME_NS +/- 292 years [1678 AD, 2262 AD]
TILEDB_DATETIME_PS +/- 106 days [1969 AD, 1970 AD]
TILEDB_DATETIME_FS +/- 2.6 hours [1969 AD, 1970 AD]
TILEDB_DATETIME_AS +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
TILEDB_DATETIME_YEAR +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
TILEDB_DATETIME_MONTH +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
TILEDB_DATETIME_WEEK +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
TILEDB_DATETIME_DAY +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
TILEDB_DATETIME_HR +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
TILEDB_DATETIME_MIN +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
TILEDB_DATETIME_SEC +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
TILEDB_DATETIME_MS +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
TILEDB_DATETIME_US +/- 2.9e5 years [290301 BC, 294241 AD]
TILEDB_DATETIME_NS +/- 292 years [1678 AD, 2262 AD]
TILEDB_DATETIME_PS +/- 106 days [1969 AD, 1970 AD]
TILEDB_DATETIME_FS +/- 2.6 hours [1969 AD, 1970 AD]
TILEDB_DATETIME_AS +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
TILEDB_DATETIME_YEAR +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
TILEDB_DATETIME_MONTH +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
TILEDB_DATETIME_WEEK +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
TILEDB_DATETIME_DAY +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
TILEDB_DATETIME_HR +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
TILEDB_DATETIME_MIN +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
TILEDB_DATETIME_SEC +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
TILEDB_DATETIME_MS +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
TILEDB_DATETIME_US +/- 2.9e5 years [290301 BC, 294241 AD]
TILEDB_DATETIME_NS +/- 292 years [1678 AD, 2262 AD]
TILEDB_DATETIME_PS +/- 106 days [1969 AD, 1970 AD]
TILEDB_DATETIME_FS +/- 2.6 hours [1969 AD, 1970 AD]
TILEDB_DATETIME_AS +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
TILEDB_DATETIME_YEAR +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
TILEDB_DATETIME_MONTH +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
TILEDB_DATETIME_WEEK +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
TILEDB_DATETIME_DAY +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
TILEDB_DATETIME_HR +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
TILEDB_DATETIME_MIN +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
TILEDB_DATETIME_SEC +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
TILEDB_DATETIME_MS +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
TILEDB_DATETIME_US +/- 2.9e5 years [290301 BC, 294241 AD]
TILEDB_DATETIME_NS +/- 292 years [1678 AD, 2262 AD]
TILEDB_DATETIME_PS +/- 106 days [1969 AD, 1970 AD]
TILEDB_DATETIME_FS +/- 2.6 hours [1969 AD, 1970 AD]
TILEDB_DATETIME_AS +/- 9.2 seconds [1969 AD, 1970 AD]
Datatype Time span (relative) Time span (absolute)
DataType.DateTimeYear +/- 9.2e18 years [9.2e18 BC, 9.2e18 AD]
DataType.DateTimeMonth +/- 7.6e17 years [7.6e17 BC, 7.6e17 AD]
DataType.DateTimeWeek +/- 1.7e17 years [1.7e17 BC, 1.7e17 AD]
DataType.DateTimeDay +/- 2.5e16 years [2.5e16 BC, 2.5e16 AD]
DataType.DateTimeHour +/- 1.0e15 years [1.0e15 BC, 1.0e15 AD]
DataType.DateTimeMinute +/- 1.7e13 years [1.7e13 BC, 1.7e13 AD]
DataType.DateTimeSecond +/- 2.9e11 years [2.9e11 BC, 2.9e11 AD]
DataType.DateTimeMillisecond +/- 2.9e8 years [2.9e8 BC, 2.9e8 AD]
DataType.DateTimeMicrosecond +/- 2.9e5 years [290301 BC, 294241 AD]
DataType.DateTimeNanosecond +/- 292 years [1678 AD, 2262 AD]
DataType.DateTimePicosecond +/- 106 days [1969 AD, 1970 AD]
DataType.DateTimeFemtosecond +/- 2.6 hours [1969 AD, 1970 AD]
DataType.DateTimeAttosecond +/- 9.2 seconds [1969 AD, 1970 AD]