About
This page contains the documentation about how Tabulify is managing time data type (ie date, timestamp, time).
Conversion
Number
An integer or a long is considered:
- for a date as the number of day since Epoch (1970-01-01)
- for a timestamp as the number of Millisecond since Epoch (1970-01-01 00:00:00)
- for a time as the number of second (since the beginning of the day)
String
Tabulify will translate automatically a date/time string in a native date/time when loading data.
This list is not exhaustive but below are the most known pattern.
| String Pattern | Type |
|---|---|
| yyyy-MM-dd | Date |
| yyyy-MM-dd HH:mm:ss.SSS | Sql Timestamp |
| yyyy-MM-ddTHH:mm:ss.SSS | Iso Timestamp |
Configuration
Storage
The following built-in connection attribute changes the storage format of time data type (ie date, timestamp and time).
Changing this value may lead to unpredictable transformation. If you change the value from epochSec to epochMs, the same number will not have the same time value. Once this settings has been set, it's not recommended to change it.
| Built-in Connection Attribute | Default Value | Description |
|---|---|---|
| DateDataType | SqlObject | Define the storage format of a Date data type (YYYY-MM-DD) |
| TimestampDataType | SqlObject | Define the storage format of a Timestamp data type (YYYY-MM-DD HH:MM:SS) |
| TimeDataType | SqlObject | Define the storage format of a Time data type (HH:MM:SS) |
The value can be chosen with any of the following values:
- Native (Default) to store the time value in the data type of the target system.
- SqlLiteral to store the time value in a text literal (ie YYYY-MM-DD, YYYY-MM-DD HH:MM:SS.SSS, or HH:MM:SS)
- epochSec to store the time value from epoch (1970-01-01) format in second unit
- epochMs to store the time value from epoch (1970-01-01) format in milli-second unit
- epochDay to store the time value from epoch (1970-01-01) format in number of day - only valid for a date
The default value may change by system. For instance, the sqlite database storage format is SqlLiteral because Sqlite does not support a native time format. More …Sqlite - Time