FirebirdSQL logo

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.

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, 1858 — whilst a time value or the time-part of a timestamp is represented as the number of deci-milliseconds (100 microseconds) 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 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.Fractional values are rounded 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.Fractional values are rounded 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

Note
Notes

In Dialect 1, the DATE type is considered an alias of TIMESTAMP.

See also

DATEADD, DATEDIFF

Supplemental Time Zone Features

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

Virtual table RDB$TIME_ZONES

A virtual table listing time zones supported in the engine.

See also RDB$TIME_ZONES in System Tables.