DateTime field types can be categorized as follows:

• DateTime (with Time Zone): These values are standardized to UTC (Coordinated Universal Time) and are subject to time zone adjustments when necessary.
• DateTime (without Time Zone): This type stores date and time data without incorporating any time zone information.
• Date (without Time): This format exclusively stores date information, omitting any time component.
• Time: Stores only time information, excluding the date.
• Unix Timestamp: This type represents the number of seconds that have elapsed since January 1, 1970, and is stored as a Unix timestamp.

Here are examples for each DateTime-related field type:

Data Source Comparisons #

Below is a comparison table for APES, MySQL, and PostgreSQL:

Note:

• MySQL’s TIMESTAMP type covers a range between 1970-01-01 00:00:01 UTC and 2038-01-19 03:14:07 UTC. For dates and times outside this range, it is recommended to use DATETIME or BIGINT to store Unix timestamps.

DateTime Storage Processing Workflow #

With Time Zone #

This includes DateTime (with Time Zone) and Unix Timestamp.

Note:

• To accommodate a broader range of dates, APES uses the DATETIME type in MySQL for DateTime (with Time Zone) fields. The date value stored is converted based on the server's TZ environment variable, meaning that if the TZ environment variable changes, the stored DateTime value will also change.
• Since there is a time zone difference between UTC and local time, directly displaying the raw UTC value could lead to user confusion.

Without Time Zone #

UTC #

UTC (Coordinated Universal Time) is the global time standard used to coordinate and synchronize time worldwide. It is a highly precise time standard, maintained by atomic clocks, and synchronized with the Earth's rotation.

The difference between UTC and local time can cause confusion when displaying raw UTC values. For example:

These different times all correspond to the same moment, merely expressed in various time zones.