EXTRACT
The EXTRACT function extracts a single field from a TIMESTAMP or INTERVAL value.
Syntax
EXTRACT (<field_name> FROM <value>)
The field_name argument is SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, or YEAR. The value argument is of type TIMESTAMP or INTERVAL.
Remarks
• The data type of the output is an exact NUMERIC with a precision equal to the leading precision of value and a scale of zero. When the field name is a SECOND, the precision is equal to the sum of the leading precision and the seconds precision of value and a scale equal to the SECOND’s precision.
• When value is a negative INTERVAL, the result is a negative value.
• If value is NULL, the result is also NULL.
EXTRACT (With INTERVAL)
SELECT orders.OrderDate,
EXTRACT (SECOND FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),
EXTRACT (MINUTE FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),
EXTRACT (HOUR FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),
EXTRACT (DAY FROM INTERVAL '2 23:51:19.124' DAY TO SECOND),
EXTRACT (MONTH FROM INTERVAL '500' MONTH(3))
EXTRACT (YEAR FROM INTERVAL '499-11' YEAR(3) TO MONTH),
FROM /shared/examples/ds_orders/orders
Results of the EXTRACT functions:
EXTRACT (SECOND FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 19.124
EXTRACT (MINUTE FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 51
EXTRACT (HOUR FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 23
EXTRACT (DAY FROM INTERVAL '2 23:51:19.124' DAY TO SECOND) = 2
EXTRACT (MONTH FROM INTERVAL '500' MONTH(3)) = 500
EXTRACT (YEAR FROM INTERVAL '499-11' YEAR(3) TO MONTH) = 499
EXTRACT (Without INTERVAL)
SELECT orders.ShipName,
orders.OrderID,
orders.OrderDate,
EXTRACT (DAY FROM orders.OrderDate) "day",
EXTRACT (MONTH FROM orders.OrderDate) "month"
EXTRACT (QUARTER FROM orders.OrderDate) "quarter"
FROM /shared/examples/ds_orders/orders orders