Timestamp Extract Functions

Timestamp extract functions fetch the corresponding date, week, or the index value from a given timestamp.

Date extract functions return the corresponding year/month/day/hour/minute/second/millisecond/microsecond/nanosecond from a timestamp.

Example: Get consolidated travel details of the passengers from airline baggage tracking data.

In an airline application, it is beneficial to the passengers to have a quick summary of their upcoming travel details. You can use miscellaneous time functions to get consolidated travel details of the passengers from the BaggageInfo table.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3

Explanation:

You can use the time functions to retrieve the travel date, month, and year. The concat string function is used to concatenate the retrieved travel records to display them in the desired format on the application. You first use the CAST expression to convert the flightDates to a TIMESTAMP and then fetch the date, month, and year details from the timestamp.

Output:

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

The query returns the flight details which can serve as a quick look-up for the passengers.

Week extract functions return the corresponding week/isoweek from a timestamp.

Example: Determine the week and ISO week number from a passenger's travel date.

SELECT
$s.fullName,
$s.contactPhone, 
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explanation: You first use the CAST expression to convert the flightDate to a TIMESTAMP and then fetch the week and isoweek from the timestamp.

Output:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}

Timestamp index extract functions return the corresponding quarter/week/month/year index from a timestamp.

Example: Find the day of the week for given timestamps.

SELECT day_of_week("2024-06-19") AS DAYVAL1, 
day_of_week(parse_to_timestamp('06/19/24', 'MM/dd/yy')) AS DAYVAL2 
FROM BaggageInfo 
WHERE ticketNo=1762344493810

Explanation: The second timestamp in the query is in an unsupported format '06/19/24' by itself, so wrap it in the parse_to_timestamp function to make it valid.

Output:

{
  "DAYVAL1" : 3,
  "DAYVAL2" : 3
}