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 12-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 12-30 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 12-31 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 12-32 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 12-33 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 12-34 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"}