The EXTRACT( ) Function - Firebird

EXTRACT( ) returns a variety of elements extracted by decoding fields of date/time types. It can operate on all dialect 3 and dialect 1 date/time fields.

Syntax

Here’s the syntax for EXTRACT( ):

EXTRACT (element FROM field)

element must be a defined element that is valid for the data type of field. Not all elements are valid for all date/time types. The data type of element varies according to the element extracted. Table enumerates the elements available for each date/ time type.

field can be a column, a variable, or an expression that evaluates to a date/time field.

Table shows the restrictions on the arguments and types when using EXTRACT( ).

EXTRACT( ) Arguments, Types, and Restrictions

Combining EXTRACT( ) with Other Functions

Following are two examples where EXTRACT( ) is used with CAST( ) to obtain date representations not available with either function on its own.

To Cast Date Plus Time Without Sub-Seconds

Although it is not possible by direct casting to return a cast date plus time string without the sub-second portion, it can be done using a complex expression involving both CAST( ) and EXTRACT( ).

To Extract a TIME String

This technique has more meaning for dialect 1 than for dialect 3. However, it can be extrapolated to any dialect 3 date or time type if you need to store time of day as a string.

The EXTRACT( ) function makes it possible to extract the individual elements of date and time types to SMALLINT values. The following trigger extracts the time elements from a dialect 1 DATE column named CAPTURE_DATE and converts them into a CHAR(13), mimicking the Firebird standard time literal 'HH:NN:SS.nnnn':

A Sample Date/Time Type Conversion Task

The CHAR(13) string stored by the trigger in the preceding example does not behave like a dialect 3 TIME type. However, by simple casting, it can be converted directly, in a later upgrade to dialect 3, to a dialect 3 TIME type.

First, we add a temporary new column to the table to store the converted time string:

The next thing we need to do is temporarily alter our trigger to remove the reference to the dialect 1 time string. This is needed to prevent dependency problems when we want to change and alter the old time string:

Now, we can drop the old CAPTURE_TIME column:

Create it again, this time as a TIME type:

Move the data from the temporary column into the newly added CAPTURE_TIME:

Drop the temporary column:

Finally, fix up the trigger so that it now writes the CAPTURE_TIME value as a TIME type:

All of these steps can be written as an SQL script. For details about SQL scripting, refer to the topic “Schema Scripts” in Chapter From Drawing Board to Database.