Tabulify - Time data type

Undraw Text Files

Tabulify - Time data type

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

Task Runner