Extract Expressions

Syntax

extract_expression ::= EXTRACT "(" id FROM expression ")"

Semantics

The extract expression extract a component from a timestamp. Specifically, the expression after the FROM keyword must return at most one timestamp or NULL. If the result of this expression is NULL or empty, the results of EXTRACT is also NULL or empty, respectively. Otherwise, the component specified by the id is returned. This id must be one of the following keywords:

YEAR: Returns the year for the timestamp, in the range -6383 ~ 9999.
MONTH: Returns the month for the timestamp, in the range 1 ~ 12.
DAY: Returns the day of month for the timestamp, in the range 1 ~ 31.
HOUR: Returns the hour of day for the timestamp, in the range 0 ~ 23.
MINUTE: Returns the minute for the timestamp, in the range 0 ~ 59.
SECOND: Returns the second for the timestamp, in the range 0 ~ 59.
MILLISECOND: Returns the fractional second in millisecond for the timestamp, in the range 0 ~ 999.
MICROSECOND: Returns the fractional second in microsecond for the timestamp, in the range 0 ~ 999999.
NANOSECOND: Returns the fractional second in nanosecond for the timestamp, in the range 0 ~ 999999999.
WEEK: Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54.
ISOWEEK: Returns the week number within the year based on IS0-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53.

There are specific built-in functions to extract each of the above components from a time stamp. For example, EXTRACT(YEAR from expr) is equivalent to year(expr). These and other built-in functions are described in Built-in Functions.

Example 6-96 Create a message that displays the bag arrival date, time, and destination airport details for a passenger from the airline baggage tracking application data

SELECT concat("Hi ",bag.fullname,", Please collect your bags from ",$station," airport on ",$t2,"/", $t3,"/", $t1," at ",$t4,":",$t5," Hrs") AS MESSAGE
FROM BaggageInfo bag, bag.bagInfo[0].bagArrivalDate AS $bagDate, bag.bagInfo[0].lastSeenStation AS $station,
EXTRACT(YEAR from (CAST($bagDate AS Timestamp(0)))) $t1,
EXTRACT(MONTH from (CAST($bagDate AS Timestamp(0)))) $t2,
EXTRACT(DAY from (CAST($bagDate AS Timestamp(0)))) $t3, 
EXTRACT(HOUR from (CAST($bagDate AS Timestamp(0)))) $t4, 
EXTRACT(MINUTE from (CAST($bagDate AS Timestamp(0)))) $t5
WHERE ticketNo = 1762344493810

Explanation: The bagArrivalDate field in the BaggageInfo table stores the date on which the checked bags are scheduled to arrive at the destination airport. You can use the EXTRACT expression to extract the date (day, month, and year) and time (hour and minutes) from the bagArrivalDate field for the specified passenger. The date is stored as a string in the JSON data in the bagInfo field. You first use the CAST expression to convert the bagArrivalDate to a timestamp and then extract the date, month, year, and so forth from the timestamp.

To display as a message, you use the concat function.

Output:

{"MESSAGE":"Hi Adam Phillips, Please collect your bags from MEL airport on 2/1/2019 at
      16:13 Hrs"}