FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

Data Types for Dates and Times

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

Time zone support is available 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 Seconds

If fractions of seconds are stored in date and time data types, Firebird stores them to ten-thousandths of a second (100 microseconds or deci-milliseconds).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.

Note
Some Useful Knowledge about Sub-second Precision

The time-part of a TIME or TIMESTAMP is a 32-bit integer, 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

  • Functions DATEADD() and DATEDIFF() support up to deci-milliseconds precision with MILLISECOND

  • 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 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 no longer match the intended value if the actual offset in that named zone changes.

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.

In Dialect 1, DATE is an alias for [fblangref50-datatypes-timestamp].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 you need 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 the same effect, with fewer keystrokes!
Storage of Dates

Internally, Firebird stores dates in a 32-bit integer as a Modified Julian Date, or the number of days since 1858-11-17.An additional restriction is imposed, limiting valid dates to the range from 0001-01-01 AD to 9999-12-31 AD.