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.


Here’s the syntax for EXTRACT( ):

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

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:

Next, populate the temporary column by casting the dialect 1 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.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics