Operations Using Date and Time Values - Firebird

The use of an arithmetic operation to manipulate, calculate, set, or condition the relationships between two dates has already been visited in the section “Interval of Time (Time Elapsed)” earlier in this chapter. The capability to subtract an earlier date, time, or timestamp value from a later one is possible because of the way Firebird stores date/time types. It uses one or two 32-bit integers to store timestamps, date-only dates, or time of day, respectively. The units represented by these numbers are days in the date-part longword and fractions of days in the time -part integer. The date part represents the number of days since “date zero,” November 17, 1898. The time part represents the ten -thousandths of a second elapsed since midnight. Dialect 3 DATE stores only the date part. Dialect 3 TIME stores only the time part. TIMESTAMP and dialect 1 DATE store both parts.

Quite simply, these number structures can be operated on, using simple addition and subtraction expressions, to calculate time differences (intervals), increment or decrement dates, and set date and time ranges. Table explains which operations are valid and the results to be achieved.

Arithmetic Involving Date/Time Data Types

General Rules for Operations

One date/time value can be subtracted from another, provided

• Both values are of the same date/time type.
• The first operand is later than the second.

A valid subtraction involving date/time types produces a scaled DECIMAL result in dialect 3 and a DOUBLE PRECISION result in dialect 1.

Date/time types cannot be added together. However, a date part can be concatenated to a time part using

• Dyadic addition syntax to concatenate a pair of fields or variables
• String concatenation to concatenate a date/time literal with another date/time literal or with a date/time field or variable

Multiplication and division operations involving date/time types are not valid.

Expressions As Operands

The operand to advance or reduce a TIMESTAMP, TIME, DATE, or dialect 1 DATE value can be a constant or an expression. An expression may be especially useful in your applications when you want to advance or reduce the value specifically in seconds, minutes, or hours, or, for example, half days rather than directly by days.

Dialect 3 uses the SQL-92 rule for integer/integer division: The result is always an integer, rounded down if necessary. When using expressions in dialect 3, ensure that one of the operands is a real number with enough decimal places to avoid the possibility of arithmetic errors or imprecision resulting from SQL-92 integer division.

Table shows some examples.

Examples of Operands Using Expressions