Timestamp index extract functions

These functions return the corresponding quarter/week/month/year index from a timestamp. The timestamp must be supplied as an argument to the function.

Syntax:

INTEGER quarter(<timestamp>)

INTEGER day_of_week(<timestamp>)

INTEGER day_of_month(<timestamp>)

INTEGER day_of_year(<timestamp>)

Semantics:

• timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type. If you supply an unsupported timestamp format, the functions return an error.

• Return Value: INTEGER

  The functions adhere to the ISO-8601 calendar system.

  Table 5 Timestamp index extraction functions

  Function	Return Value
  quarter	1 to 4 1 indicates Quarter 1, and 4 indicates Quarter 4.
  day_of_week	1 to 7 1 indicates the first day of the week (Monday) and 7 is the last day of the week (Sunday).
  day_of_month	1 to 31 1 indicates the first day of the month and 31 is the last day of the month.
  day_of_year	1 to 366 1 indicates the first day of the year and 366 is the last day of the year.

Note:

The functions return NULL in the following cases:

• If the timestamp argument is set to NULL.

• If the input timestamp is not castable to TIMESTAMP type.

Example 1 - Print the quarter in which a subscriber viewed the given episode from a series

SELECT quarter(show.acct_data.contentStreamed[1].seriesInfo[0].episodes[1].date) AS quarter
FROM stream_acct show
WHERE profile_name="AP"

Output:

{"quarter":1}

Example 2 - 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
}

Example 3 - From the airline baggage tracking data, print the flight numbers, bag arrival date, and the day on which the checked bags arrive at MEL airport

SELECT DISTINCT $b.flightLegs[].flightNo,
$b.bagArrivalDate,
day_of_week($b.bagArrivalDate) AS DAY
FROM BaggageInfo bag, bag.bagInfo[0] AS $b
WHERE regex_like($b.routing,".*/MEL")

Explanation: You use the day_of_week function to determine the day index corresponding to the timestamp value in the bagArrivalDate field.

To display the results for the MEL airport, use the regular expression condition to match the pattern of the destination airport with the string “MEL”.

Output:

{"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-25T20:15:00Z","DAY":1}
{"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-04T10:08:00Z","DAY":1}
{"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-16T16:13:00Z","DAY":6}
{"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-01T16:13:00Z","DAY":5}

Example 4 - From the airline baggage tracking data, print the flight numbers, departure date, and the day of the month for all the flights starting from SFO airport

SELECT DISTINCT $f.flightNo AS FLIGHT,
$f.flightDate AS DEPARTURE,
day_of_month($f.flightDate) AS DAY
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE $f.fltRouteSrc =any "SFO"

Output:

{"FLIGHT":"BM318","DEPARTURE":"2019-02-02T12:00:00Z","DAY":2}
{"FLIGHT":"BM318","DEPARTURE":"2019-03-12T03:00:00Z","DAY":12}
{"FLIGHT":"BM572","DEPARTURE":"2019-03-02T05:00:00Z","DAY":2}
{"FLIGHT":"BM318","DEPARTURE":"2019-03-07T04:00:00Z","DAY":7}

Example 5 - From the airline baggage tracking data, find the passengers who traveled on the 60th day of the launch of flight “BM114”

SELECT fullName, $f.fltRouteSrc AS BOARDING,
$f.flightNo AS FLIGHT,
$f.flightDate AS DEPARTURE
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE day_of_year($f.flightDate)= 60 AND $f.flightNo =any "BM114"

Output:

{"fullName":"Omar Harvey","BOARDING":"MEL","FLIGHT":"BM114","DEPARTURE":"2019-03-01T12:00:00Z"}