Data Types for Dates and Times

Table of Contents

DATE
TIME
TIMESTAMP
Operations Using Date and Time Values

The DATE, TIME and TIMESTAMP data types are used to work with data containing dates and times. Dialect 3 supports all the three 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 Seconds: If 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 in Dialect 3 databases of ODS 11 or higher.

Some useful knowledge about subseconds precision:

	Some useful knowledge about subseconds precision:
The time-part of a TIME or TIMESTAMP is a 4-byte WORD, with room for decimilliseconds 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 defaults to seconds precision and can be specified up to milliseconds precision with `CURRENT_TIME (0\|1\|2\|3)` CURRENT_TIMESTAMP milliseconds precision. Precision from seconds to milliseconds can be specified with `CURRENT_TIMESTAMP (0\|1\|2\|3)` Literal 'NOW': milliseconds precision Functions DATEADD() and DATEDIFF() support up to milliseconds precision. Deci-milliseconds can be specified but they are rounded to the nearest integer before any operation is performed The EXTRACT() function returns up to deci-milliseconds precision with the SECOND and MILLISECOND arguments For TIME and TIMESTAMP literals Firebird happily accepts up to deci-milliseconds precision, but truncates (not rounds) the time part to the nearest lower or equal millisecond. Try, for example, `SELECT TIME '14:37:54.1249' FROM rdb$database` the '+' and '-' operators work with deci-milliseconds precision, but only within the expression. As soon as something is stored or even just SELECTed from RDB$DATABASE, it reverts to milliseconds precision Deci-milliseconds precision is rare and is not currently stored in columns or variables. 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.

The time-part of a TIME or TIMESTAMP is a 4-byte WORD, with room for decimilliseconds 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 defaults to seconds precision and can be specified up to milliseconds precision with CURRENT_TIME (0|1|2|3)
CURRENT_TIMESTAMP milliseconds precision. Precision from seconds to milliseconds can be specified with CURRENT_TIMESTAMP (0|1|2|3)
Literal 'NOW': milliseconds precision
Functions DATEADD() and DATEDIFF() support up to milliseconds precision. Deci-milliseconds can be specified but they are rounded to the nearest integer before any operation is performed
The EXTRACT() function returns up to deci-milliseconds precision with the SECOND and MILLISECOND arguments
For TIME and TIMESTAMP literals Firebird happily accepts up to deci-milliseconds precision, but truncates (not rounds) the time part to the nearest lower or equal millisecond. Try, for example, SELECT TIME '14:37:54.1249' FROM rdb$database
the '+' and '-' operators work with deci-milliseconds precision, but only within the expression. As soon as something is stored or even just SELECTed from RDB$DATABASE, it reverts to milliseconds precision

Deci-milliseconds precision is rare and is not currently stored in columns or variables. 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.

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

	Tip
In Dialect 1, date literals without a time part, as well as '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 `'25.12.2016 00:00:00.0000'`. However, `'25.12.2016'` would have precisely the same effect, with fewer keystrokes!

In Dialect 1, date literals without a time part, as well as '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 '25.12.2016 00:00:00.0000'. However, '25.12.2016' would have precisely the same effect, with fewer keystrokes!

TIME

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 and Variables.

TIMESTAMP

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.

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.3. Arithmetic Operations for Date and Time Data Types

Operand 1	Operation	Operand 2	Result
DATE	+	TIME	TIMESTAMP
DATE	+	Numeric value `n`	DATE increased by `n` whole days. Broken values are rounded (not floored) to the nearest integer
TIME	+	DATE	TIMESTAMP
TIME	+	Numeric value `n`	TIME 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
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	-	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	-	Numeric value `n`	TIMESTAMP where the date will retreat by the number of days and part of a day represented by number `n`—so “- 2.25” will reduce the date by 2 days and 6 hours

	Notes
	The DATE type is considered as TIMESTAMP in Dialect 1.