Time Zone Format

SQL Language StructureCommon Language Elements

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`.