Skip to main content

Date/Time Types

Relyt supports the main date and time data types, as shown in the following table.

NameAliasStorage sizeValue rangeResolutionDescription
dateN/A4 bytes0001-01-01 to 9999-12-311 dayMeasures the number of days since January 1, 1970.
It is insensitive to time zone differences.
timestamptimestamp without time zone8 bytes0001-01-01 00:00:00 to 9999-12-31 23:59:591 microsecond/14 digitsStores the number of seconds that have elapsed since January 1, 1970, excluding the time zone.
timestamp with time zonetimestamptz8 bytes0001-01-01 00:00:00 to 9999-12-31 23:59:59 (UTC)1 microsecond/14 digitsStores the number of seconds that have elapsed since January 1, 1970, including the time zone.
intervalN/A16 bytes-178000000 years to 178000000 years1 microsecond/14 digitsThe time interval.
It is sensitive to time zone differences.

important

Relyt does not allow defining columns of data type interval in DDL because Relyt does not support the storage of intervals.


For information about the operations that can be performed on these data types, see Date/Time Functions and Operators in the PostgreSQL documentation.

The output styles of date/time types are determined by the client you use, and thus are not included in this guide.

note
  • timestamptz can be used as an abbreviation of timestamp with time zone.
  • Dates are counted as per the Gregorian calendar, even in years prior to the introduction of the calendar. For more details, see History of Units in the PostgreSQL documentation.

Dates​

Relyt supports date/time input in ISO 8601.

Enclose any date or time literal input by using a pair of single quotation marks (' '), for example, '2022-07-01' or '2022-7-1'.

Timestamps​

A timestamp type accepts valid input in a concatenated format of a date and time.

Relyt supports two timestamp types: timestamp with time zone and timestamp to recommend you determine whether the time zone is required before you specify the value. If you choose timestamp with time zone, use a +/- symbol to specify the time zone offset. Relyt ignores time zone indications in a timestamp without time zone literal.

For timestamp with time zone, Relyt internally stores values in UTC. An input value with an explicit time zone is converted to UTC using the right offset. If an input string lacks a time zone indication, the value of the TimeZone system parameter is used as the time zone. The conversion to UTC uses the UTC offset for that time zone.

When a timestamp with time zone value is output, the value displayed is local time in the time zone after being converted from UTC.

If you convert a value between the timestamp with time zone type and the timestamp type, the timestamp value is normally regarded as the timezone local time.

Important

Relyt does not allow the use of uppercase letter T to concatenate the date part and the time part in a value. Use a space instead.


Relyt supports two input styles of timestamps.

Style specificationFormatExampleDescription
ISOFull time zone name2022-07-01 12:00:00 Asia/ShanghaiA full time zone name, for example, Asia/Shanghai. Use the widely-used IANA time zone data for this purpose so that the same time zone names can also be recognized by other software.
POSIXOffset2022-07-01 12:00:00+8 or
2022-07-01 12:00:00+8:01		The offset fields specify the hours, and optionally minutes and seconds, difference from UTC. They have the format "hh[:mm]" optionally with a leading sign (+ or -).

Time zones​

Time zones and their standards are influenced by political decisions, not just by physical geography. Although time zones around the world have gradually become standardized since the 1900s, arbitrary changes still occur, especially regarding daylight saving time rules. For standardization purposes, Relyt uses the universal IANA (Olson) time zone database to obtain information about historical time zone rules. For future times, Relyt assumes that the known time zones will continue to be observed indefinitely.

Relyt strives to be compatible with the typical usage of SQL standards. However, the SQL standard has some peculiar mixes of date and time types and their functions. Two notable issues are:

  1. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.
  2. The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

To address these issues, Relyt recommends that you specify the time zone when using date/time types that include both date and time.

All time zone-aware dates and times are stored internally in UTC. Before being displayed to the client, they are converted to the local time of the time zone specified by the TimeZone configuration parameter.

Supported time zone formats​

Relyt supports three time zone formats:

  • Full time zone names

    The IANA time zone database is used to represent the time difference of a geographic area in relation to Coordinated Universal Time (UTC), such as America/New_York and Europe/Paris. Each geographic identifier corresponds to a specific time zone, with the full name indicating the time difference relative to UTC, measured in hours. This difference can be positive, negative, or zero. A positive number means the time zone is east of UTC, a negative number indicates it is west of UTC, and zero means it is the same as UTC. For instance, the America/New_York time zone has an offset of -5 hours, indicating it is 5 hours behind UTC.

  • Time zone offset representations based on the ISO-8601 standard

    The time zone format follows this pattern: "±HH[:MM]". It signifies an offset in hours and optionally minutes relative to UTC. This offset can be positive, negative, or zero. A positive offset means the time zone is ahead of UTC, a negative offset means it is behind, and zero means it is the same as UTC. For example, +02:00 signifies a time zone 2 hours ahead of UTC, while -05:30 means a time zone 5 hours and 30 minutes behind UTC.

  • Time zone offset representations based on the POSIX standard

    The time zone format follows this pattern: "±HH[:MM]". It signifies an offset in hours and optionally minutes relative to UTC. This offset can be positive, negative, or zero. A positive offset means the time zone is behind UTC, a negative offset means it is ahead of UTC, and zero means it is the same as UTC. (The offset direction is opposite to that of the ISO-8601 standard) For instance, +02:00 means 2 hours behind UTC, while -05:30 means 5 hours and 30 minutes ahead of UTC.

Time zone offset description​

The standard used for time zone offsets varies across different scenarios. Here are the descriptions of time zone offsets in various contexts:


Scenario 1: Time literals

Compliant standard: ISO-8601

Description: Time zone offsets in time literals adhere to the ISO-8601 standard.

Examples:

SELECT timestamptz '2024-01-15 12:00:00+06';

      timestamptz
------------------------
 2024-01-15 06:00:00+00
(1 row)

SELECT timestamptz '2024-01-15 12:00:00+06:00';

      timestamptz
------------------------
 2024-01-15 06:00:00+00
(1 row)
note

In the above examples, the session time zone is set to UTC.


Scenario 2: Time output

Compliant standard: ISO-8601

Description: Time zone offsets in time output follow the ISO-8601 standard.

Examples:

SELECT timestamptz '2024-01-15 12:00:00+00';

      timestamptz
------------------------
 2024-01-15 20:00:00+08
(1 row)
note

In the above example, the session time zone is set to Asia/Shanghai.


Scenario 3: Covert strings to date/time

Compliant standard: ISO-8601

Description: Time zone offsets represented as strings are processed according to the ISO-8601 standard.

Examples:

SELECT CAST('2024-01-15 12:00:00+06' AS timestamptz);

      timestamptz
------------------------
 2024-01-15 06:00:00+00
(1 row)

SELECT CAST('2024-01-15 12:00:00+06:00' AS timestamptz);

      timestamptz
------------------------
 2024-01-15 06:00:00+00
(1 row)
note

In the above examples, the session time zone is set to UTC.


Scenario 4: Date/time formatting functions

This scenario contains two cases:

  1. Convert strings to date/time

    Compliant standard: ISO-8601

    Description: Time zone offsets in the input of date/time formatting functions adhere to the ISO-8601 standard.

    Examples:

    SELECT to_timestamp('2024-01-15 12:00:00-08', 'YYYY-MM-DD HH24:MI:SSTZH');

        to_timestamp
    ------------------------
    2024-01-15 20:00:00+00
    (1 row)

    SELECT to_timestamp('2024-01-15 12:00:00-08:30', 'YYYY-MM-DD HH24:MI:SSTZH:TZM');

          to_timestamp
    ------------------------
     2024-01-15 20:30:00+00
    (1 row)
    note

    In the above examples, the session time zone is set to UTC.

  2. Convert date/time to strings

    Compliant standard: See the compliant standard in "Scenario 5: Time zones in sessions".

    Description: The 'TZH' format specifier utilizes the session time zone offset for formatting date/time to strings.

    Examples:

    SELECT to_char(timestamptz '2024-01-15 12:00:00+10', 'YYYY-MM-DD HH24:MI:SS TZH:TZM');

          to_char
    ----------------------------
    2024-01-15 10:00:00+08:00
    (1 row)
    note

    In the above example, the session time zone is set to '+8'.


Scenario 5: Time zones in sessions

This scenario contains two cases:

  1. Session time zone set in the ±HH format

    Compliant standard: ISO-8601

    Description: When the session time zone is set in the '±HH' format, offsets adhere to the ISO-8601 standard.

    Examples:

    SET time zone '+07';

    SELECT timestamp with time zone '2024-02-25 10:00:00+00';

          timestamptz
    ------------------------
     2024-02-25 17:00:00+07
    (1 row)


    SET time zone '-07';

    SELECT timestamp with time zone '2024-02-25 10:00:00+00';

          timestamptz
    ------------------------
     2024-02-25 03:00:00-07
    (1 row)
    note

    In this example, the session time zone is set to '+07'.


  2. Session time zone set in the ±HH:MM format

    Compliant standard: POSIX

    Description: When the session time zone is set in the '±HH:MM' format, offsets adhere to the POSIX standard.

    Examples:

    SET time zone '+07:00';

    SELECT timestamp with time zone '2024-02-25 10:00:00+00';

          timestamptz
    ------------------------
     2024-02-25 03:00:00-07
    (1 row)


    SET time zone '-07:00';



    SELECT timestamp with time zone '2024-02-25 10:00:00+00';

          timestamptz
    ------------------------
     2024-02-25 17:00:00+07
    (1 row)
    note

    In the above examples, the session time zone is set to '+07:00'.


Scenario 6: Time zone in the AT TIME ZONE command

Compliant standard: POSIX

Description: Time zone settings in the AT TIME ZONE command, in the ±HH/±HH:MM offset format, follow the POSIX standard.

Examples:

SET time zone 'UTC';

SELECT timestamptz '2024-01-10 12:00:00+00' at time zone '+08' ;


      timezone
---------------------
 2024-01-10 04:00:00
(1 row)

SELECT timestamptz '2024-01-10 12:00:00+00' at time zone '+08:00' ;

      timezone
---------------------
 2024-01-10 04:00:00
(1 row)

SELECT timestamp '2024-01-10 12:00:00' at time zone '+08';

        timezone
------------------------
 2024-01-10 20:00:00+00
(1 row)

SELECT timestamp '2024-01-10 12:00:00' at time zone '+08:00';


        timezone
------------------------
 2024-01-10 20:00:00+00
(1 row)
note

In the above examples, the session time zone is set to UTC.

Intervals​

The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
MILLISECOND
MICROSECOND

Following is an example of an interval value:

1 year 2 months 3 days 4 hours 5 minutes 6 seconds
AbbreviationMeaning
YYears
MMonths (in the date part)
WWeeks
DDays
HHours
MMinutes (in the time part)
SSeconds