Previous Next JavaScript must be enabled to correctly display this content

## timestamp_floor or timestamp_trunc function

The `timestamp_floor` or `timestamp_trunc` function returns the rounded-down value of the given timestamp to the specified unit. The functions can be used interchangeably in a query.

If the input timestamp value is already rounded down to the specified unit, then the return value is the same as the input timestamp value.

Syntax:

    TIMESTAMP timestamp_floor(<timestamp>[, unit])
    TIMESTAMP timestamp_trunc(<timestamp>[, unit])

Semantics:

- timestamp: The `timestamp` argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type.
- unit: The `unit` argument is optional and a STRING data type. If not specified, DAY is the default unit. For more details, see Supported units.
- Return Value: TIMESTAMP(0)
  The function returns NULL in the following cases:
  - If either the `timestamp` or `unit` argument is set to NULL.
  - If the input `timestamp` is not castable to TIMESTAMP type.

Example 12-17 Print the name, flight number, and travel date for all the passengers who boarded at originating airport JFK in the month of March 2019

    SELECT bag.fullName, $f.flightNo, $f.flightDate
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
    WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explanation: You use the `timestamp_floor` function with the unit value as MONTH to round down the travel dates to the beginning of the month. You then compare the resulting timestamp value with the string "2019-03-01" to select the desired passengers. This query does not consider the passengers in transit.

This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To avoid the duplication of results due to multiple checked bags by a passenger, you consider only the first element of the `bagInfo` array in this query.

Output:

    {"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
    {"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Example 12-18 Fetch the flight number, flight departure time, and security check-in time for a passenger

    SELECT $b.flightLegs[0].flightNo,
    $b.flightLegs[0].flightDate,
    timestamp_add(timestamp_trunc($b.flightLegs[0].flightDate, 'HOUR'), '-2 HOURS') AS SECURITYCHECK
    FROM BaggageInfo bag, bag.bagInfo[0] AS $b
    WHERE ticketNo=1762344493810

Explanation: Usually in an airline, the passengers are allowed to proceed through security check approximately two hours before the departure. To calculate the security check-in time, you subtract two hours from the flight departure time. You first approximate the `flightDate` value by using the`timestamp_trunc` function with HOUR as unit to round down the `flightDate` to the beginning of the hour. Supply the resulting timestamp to the timestamp_add function with a negative value of '-2 HOURS' to subtract the truncated flight departure time by two hours.

To avoid the duplication of results due to multiple checked baggage by a passenger, you consider only the first element of the `bagInfo` array in this query.

Output:

    {"flightNo":"BM604","flightDate":"2019-02-01T06:00:00Z","SECURITYCHECK":"2019-02-01T04:00:00.000000000Z"}