1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. timestamp_floor or timestamp_trunc function

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"}