Chapter 3. Data Types and Subtypes - 3.4 Data Types for Dates and Times - 《Firebird 4.0 Language Reference》

3.4 Data Types for Dates and Times

3.4 Data Types for Dates and Times

The DATE, TIME and TIMESTAMP data types are used to work with data containing dates and times.

Firebird 4.0 introduces time zone support, using the types TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE. In this language reference, we’ll use TIME and TIMESTAMP to refer both to the specific types without time zone — TIME [WITHOUT TIME ZONE] and TIMESTAMP [WITHOUT TIME ZONE] — and aspects of both the without time zone and with time zone types, which one we mean is usually clear from the context.

Important

The data types TIME WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE and DATE are defined to use the session time zone when converting from or to a TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE. TIME and TIMESTAMP are synonymous to their respective WITHOUT TIME ZONE data types.

Dialect 3 supports all the five types, while Dialect 1 has only DATE. The DATE type in Dialect 3 is date-only, whereas the Dialect 1 DATE type stores both date and time-of-day, equivalent to TIMESTAMP in Dialect 3. Dialect 1 has no date-only type.

Note

Dialect 1 DATE data can be defined alternatively as TIMESTAMP and this is recommended for new definitions in Dialect 1 databases.

Fractions of SecondsIf fractions of seconds are stored in date and time data types, Firebird stores them to ten-thousandths of a second. If a lower granularity is preferred, the fraction can be specified explicitly as thousandths, hundredths or tenths of a second, or second, in Dialect 3 databases of ODS 11 or higher.

Some Useful Knowledge about Subseconds Precision

The time-part of a TIME or TIMESTAMP is a 4-byte WORD, with room for deci-milliseconds (or 100 microseconds) precision and time values are stored as the number of deci-milliseconds elapsed since midnight. The actual precision of values stored in or read from time(stamp) functions and variables is:

CURRENT_TIME and LOCALTIME default to seconds precision and can be specified up to milliseconds precision with CURRENT_TIME (0|1|2|3) or LOCALTIME (0|1|2|3)
CURRENT_TIMESTAMP and LOCALTIMESTAMP default to milliseconds precision. Precision from seconds to milliseconds can be specified with CURRENT_TIMESTAMP (0|1|2|3) or LOCALTIMESTAMP (0|1|2|3)
Literal 'NOW' defaults to milliseconds precision
Function DATEADD() supports up to deci-milliseconds precision with MILLISECOND
Function DATEDIFF() only supports up to milliseconds precision
The EXTRACT() function returns up to deci-milliseconds precision with the SECOND and MILLISECOND arguments
the + and - operators work with deci-milliseconds precision.

Deci-milliseconds precision is rare and is not supported by all drivers and access components. The best assumption to make from all this is that, although Firebird stores TIME and the TIMESTAMP time-part values as the number of deci-milliseconds (10-4 seconds) elapsed since midnight, the actual precision could vary from seconds to milliseconds.

Storage of Time Zone Types

The time zone types are stored as values at UTC (offset 0), using the structure of TIME or TIMESTAMP + two extra bytes for time zone information (either an offset in minutes, or the id of a named time zone). Storing as UTC allows Firebird to index and compare two values in different time zones.

Storing at UTC has some caveats:

When you use named zones, and the time zone rules for that zone change, the UTC time stays the same, but the local time in the named zone may change.
For TIME WITH TIME ZONE, calculating a time zone offset for a named zone to get the local time in the zone applies the rules valid at the 1st of January 2020 to ensure a stable value. This may result in unexpected or confusing results.
When the rules of a named time zone changes, a value in the affected date range may longer match the intended value if the actual offset in that named zone changes.

3.4.1 `DATE`

Syntax

DATE

The DATE data type in Dialect 3 stores only date without time. The available range for storing data is from January 01, 1 to December 31, 9999.

Dialect 1 has no date-only type.

Tip

In Dialect 1, date literals without a time part, as well as casts of date mnemonics 'TODAY', 'YESTERDAY' and 'TOMORROW' automatically get a zero time part.

If, for some reason, it is important to you to store a Dialect 1 timestamp literal with an explicit zero time-part, the engine will accept a literal like '2016-12-25 00:00:00.0000'. However, '2016-12-25' would have precisely the same effect, with fewer keystrokes!

3.4.2 `TIME`

Syntax

TIME [{ WITHOUT | WITH } TIME ZONE]

For a bare TIME, WITHOUT TIME ZONE is assumed.

The TIME data type is available in Dialect 3 only. It stores the time of day within the range from 00:00:00.0000 to 23:59:59.9999.

If you need to get the time-part from DATE in Dialect 1, you can use the EXTRACT function.

Examples Using EXTRACT()

EXTRACT (HOUR FROM DATE_FIELD)
EXTRACT (MINUTE FROM DATE_FIELD)
EXTRACT (SECOND FROM DATE_FIELD)

See also the EXTRACT() function in the chapter entitled Built-in Functions.

3.4.2.1 `TIME [WITHOUT TIME ZONE]`

The TIME (or synonym TIME WITHOUT TIME ZONE) represents a time without time zone information.

3.4.2.2 `TIME WITH TIME ZONE`

The TIME WITH TIME ZONE represents a time with time zone information (either an offset or a named zone).

Firebird uses the ICU implementation of the IANA Time Zone Database for named zones.

Examples Using EXTRACT()

EXTRACT (TIMEZONE_HOUR FROM TIME_TZ_FIELD)
EXTRACT (TIMEZONE_MINUTE FROM TIME_TZ_FIELD)

3.4.3 `TIMESTAMP`

Syntax

TIMESTAMP [{ WITHOUT | WITH } TIME ZONE]

For a bare TIMESTAMP, WITHOUT TIME ZONE is assumed.

The TIMESTAMP data type is available in Dialect 3 and Dialect 1. It comprises two 32-bit words — a date-part and a time-part — to form a structure that stores both date and time-of-day. It is the same as the DATE type in Dialect 1.

The EXTRACT function works equally well with TIMESTAMP as with the Dialect 1 DATE type.

3.4.3.1 `TIMESTAMP [WITHOUT TIME ZONE]`

The TIMESTAMP (or synonym TIMESTAMP WITHOUT TIME ZONE) represents a time and date without time zone information.

3.4.3.2 `TIMESTAMP WITH TIME ZONE`

The TIMESTAMP WITH TIME ZONE represents a time with time zone information (either an offset or a named zone).

3.4.4 Session Time Zone

As the name implies, the session time zone, can be different for each database attachment. It can be set explicitly in the DPB with the item isc_dpb_session_time_zone; otherwise, by default, it uses the same time zone as the operating system of the Firebird server process. This default can be overridden in firebird.conf, setting DefaultTimeZone.

Note

Drivers may apply different defaults, for example specifying the client time zone as the default session time zone. Check your driver documentation for details.

Subsequently, the time zone can be changed to a given time zone using a SET TIME ZONE statement or reset to its original value with SET TIME ZONE LOCAL.

3.4.5 Time Zone Format

A time zone is specified as a string, either a time zone region (for example, 'America/Sao_Paulo') or a displacement from GMT in hours:minutes (for example, '-03:00').

A time/timestamp with time zone is considered equal to another time/timestamp with time zone if their conversions to UTC are equivalent. For example, time '10:00 -02:00' and time '09:00 -03:00' are equivalent, since both are the same as time '12:00 GMT'.

Important

The same equivalence applies in UNIQUE constraints and for sorting purposes.

3.4.6 Operations Using Date and Time Values

The method of storing date and time values makes it possible to involve them as operands in some arithmetic operations. In storage, a date value or date-part of a timestamp is represented as the number of days elapsed since date zero — November 17, 1898 — whilst a time value or the time-part of a timestamp is represented as the number of seconds (with fractions of seconds taken into account) since midnight.

An example is to subtract an earlier date, time or timestamp from a later one, resulting in an interval of time, in days and fractions of days.

Table 3.4.6.1 Arithmetic Operations for Date and Time Data Types

Operand 1	Operation	Operand 2	Result
`DATE`	`+`	`TIME`	`TIMESTAMP`
`DATE`	`+`	`TIME WITH TIME ZONE`	`TIMESTAMP WITH TIME ZONE`
`DATE`	`+`	Numeric value `n`	`DATE` increased by n whole days. Broken values are rounded (not floored) to the nearest integer
`TIME`	`+`	`DATE`	`TIMESTAMP`
`TIME WITH TIME ZONE`	`+`	`DATE`	`TIMESTAMP WITH TIME ZONE`
`TIME`	`+`	Numeric value `n`	`TIME` increased by n seconds. The fractional part is taken into account
`TIME WITH TIME ZONE`	`+`	Numeric value `n`	`TIME WITH TIME ZONE` increased by n seconds. The fractional part is taken into account
`TIMESTAMP`	`+`	Numeric value `n`	`TIMESTAMP`, where the date will advance by the number of days and part of a day represented by number n — so `+ 2.75` will push the date forward by 2 days and 18 hours
`TIMESTAMP WITH TIME ZONE`	`+`	Numeric value `n`	`TIMESTAMP WITH TIME ZONE`, where the date will advance by the number of days and part of a day represented by number n — so `+ 2.75` will push the date forward by 2 days and 18 hours
`DATE`	`-`	`DATE`	Number of days elapsed, within the range `DECIMAL(9, 0)`
`DATE`	`-`	Numeric value `n`	`DATE` reduced by n whole days. Broken values are rounded (not floored) to the nearest integer
`TIME`	`-`	`TIME`	Number of seconds elapsed, within the range `DECIMAL(9, 4)`
`TIME`	`-`	`TIME WITH TIME ZONE`	The without time zone value is converted to WITH TIME ZONE in the current session time zone. Number of seconds elapsed between the UTC values, within the range `DECIMAL(9, 4)`. Also applies when swapping types.
`TIME WITH TIME ZONE`	`-`	`TIME WITH TIME ZONE`	Number of seconds elapsed between the UTC values, within the range `DECIMAL(9, 4)`
`TIME`	`-`	Numeric value `n`	`TIME` reduced by n seconds. The fractional part is taken into account
`TIMESTAMP`	`-`	`TIMESTAMP`	Number of days and part-day, within the range `DECIMAL(18, 9)`
`TIMESTAMP`	`-`	`TIMESTAMP WITH TIME ZONE`	The without time zone value is converted to WITH TIME ZONE in the current session time zone. Number of days and part-day between UTC values, within the range `DECIMAL(18, 9)`. Also applies when swapping types.
`TIMESTAMP WITH TIME ZONE`	`-`	`TIMESTAMP WITH TIME ZONE`	Number of days and part-day between UTC values, within the range `DECIMAL(18, 9)`
`TIMESTAMP`	`-`	Numeric value `n`	`TIMESTAMP` where the date will decrease by the number of days and part of a day represented by number n — so `- 2.25` will decrease the date by 2 days and 6 hours

Notes

The DATE type is considered as TIMESTAMP in Dialect 1.

3.4.7 Supplemental Time Zone Features

Firebird 4.0 provides a number of features to discover time zone information.

3.4.7.1 Virtual table `RDB$TIME_ZONES`

A virtual table listing time zones supported in the engine.

See also RDB$TIME_ZONES in System Tables.

3.4.7.2 Package `RDB$TIME_ZONE_UTIL`

A package of time zone utility functions and procedures:

3.4.7.2.1 Function `DATABASE_VERSION`

RDB$TIME_ZONE_UTIL.DATABASE_VERSION returns the version of the time zone database as a VARCHAR(10) CHARACTER SET ASCII.

Example

select rdb$time_zone_util.database_version() from rdb$database;

Returns:

DATABASE_VERSION
================
2021a

3.4.7.2.2 Procedure TRANSITIONS

RDB$TIME_ZONE_UTIL.TRANSITIONS returns the set of rules between the start and end timestamps for a named time zone.

The input parameters are:

RDB$TIME_ZONE_NAME type CHAR(63)
RDB$FROM_TIMESTAMP type TIMESTAMP WITH TIME ZONE
RDB$TO_TIMESTAMP type TIMESTAMP WITH TIME ZONE

Output parameters:

RDB$START_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The start timestamp of the transition

RDB$END_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The end timestamp of the transition

RDB$ZONE_OFFSET

type SMALLINT — The zone’s offset, in minutes

RDB$DST_OFFSET

type SMALLINT — The zone’s DST offset, in minutes

RDB$EFFECTIVE_OFFSET

type SMALLINT — Effective offset (ZONE_OFFSET + DST_OFFSET)

Example

select *
  from rdb$time_zone_util.transitions(
    'America/Sao_Paulo',
    timestamp '2017-01-01',
    timestamp '2019-01-01');

Returns (RDB$ prefix left off for brevity):

             START_TIMESTAMP                END_TIMESTAMP ZONE_OFFSET DST_OFFSET EFFECTIVE_OFFSET
============================ ============================ =========== ========== ================
2016-10-16 03:00:00.0000 GMT 2017-02-19 01:59:59.9999 GMT       -180        60             -120
2017-02-19 02:00:00.0000 GMT 2017-10-15 02:59:59.9999 GMT       -180         0             -180
2017-10-15 03:00:00.0000 GMT 2018-02-18 01:59:59.9999 GMT       -180        60             -120
2018-02-18 02:00:00.0000 GMT 2018-10-21 02:59:59.9999 GMT       -180         0             -180
2018-10-21 03:00:00.0000 GMT 2019-02-17 01:59:59.9999 GMT       -180        60             -120

3.4.7.3 Updating the Time Zone Database

Time zones are often changed: of course, when it happens, it is desirable to update the time zone database as soon as possible.

Firebird stores WITH TIME ZONE values translated to UTC time. Suppose a value is created with one time zone database, and a later update of that database changes the information in the range of our stored value. When that value is read, it will be returned as different to the value that was stored initially.

Firebird uses the IANA time zone database through the ICU library. The ICU library presented in the Firebird kit (Windows), or installed in a POSIX operating system, can sometimes have an outdated time zone database.

An updated database can be found on this page on the FirebirdSQL GitHub. Filename le.zip stands for little-endian and is the necessary file for most computer architectures (Intel/AMD compatible x86 or x64), while be.zip stands for big-endian architectures and is necessary mostly for RISC computer architectures. The content of the zip file must be extracted in the /tzdata sub-directory of the Firebird installation, overwriting existing *.res files belonging to the database.

Note

/tzdata is the default directory where Firebird looks for the time zone database. It can be overridden with the ICU_TIMEZONE_FILES_DIR environment variable.

3.4 Data Types for Dates and Times

3.4 Data Types for Dates and Times

3.4.1 DATE

3.4.2 TIME

3.4.2.1 TIME [WITHOUT TIME ZONE]

3.4.2.2 TIME WITH TIME ZONE

3.4.3 TIMESTAMP

3.4.3.1 TIMESTAMP [WITHOUT TIME ZONE]

3.4.3.2 TIMESTAMP WITH TIME ZONE

3.4.4 Session Time Zone

3.4.5 Time Zone Format

3.4.6 Operations Using Date and Time Values

3.4.7 Supplemental Time Zone Features

3.4.7.1 Virtual table RDB$TIME_ZONES

3.4.7.2 Package RDB$TIME_ZONE_UTIL

3.4.7.2.1 Function DATABASE_VERSION

3.4.7.2.2 Procedure TRANSITIONS

3.4.7.3 Updating the Time Zone Database

3.4.1 `DATE`

3.4.2 `TIME`

3.4.2.1 `TIME [WITHOUT TIME ZONE]`

3.4.2.2 `TIME WITH TIME ZONE`

3.4.3 `TIMESTAMP`

3.4.3.1 `TIMESTAMP [WITHOUT TIME ZONE]`

3.4.3.2 `TIMESTAMP WITH TIME ZONE`

3.4.7.1 Virtual table `RDB$TIME_ZONES`

3.4.7.2 Package `RDB$TIME_ZONE_UTIL`

3.4.7.2.1 Function `DATABASE_VERSION`