ceil, floor

On this page

Rounds a timestamp up or down to a multiple of a time unit.

Syntax

ceil(<timestamp>, n => <n>, unit => <unit>)
floor(<timestamp>, n => <n>, unit => <unit>)

Arguments

ArgumentTypeDescription
<timestamp>timestampTimestamp to round.
nintConstant. Positive number of units per boundary. Must not be NULL.
unitstringConstant. Unit: 'hour', 'minute', 'second', 'millisecond', 'microsecond', or 'nanosecond'. Must not be NULL.

Returns

Returns timestamp, or NULL for a NULL input.

  • ceil returns the smallest boundary timestamp greater than or equal to the input.
  • floor returns the greatest boundary timestamp less than or equal to the input.
  • A timestamp already on a boundary stays unchanged.

Usage notes

Boundaries are measured from the Unix epoch in UTC.

Examples

Find the lower and upper quarter-hour boundaries around an event timestamp.

VALUES ('2026-06-01T10:07:32Z'), ('2026-06-01T10:15:00Z')
SELECT $0::timestamp AS event_time
SELECT
    event_time,
    floor(event_time, n => 15, unit => 'minute') AS lower_bound,
    ceil(event_time, n => 15, unit => 'minute') AS upper_bound
+----------------------+----------------------+----------------------+
| event_time           | lower_bound          | upper_bound          |
+----------------------+----------------------+----------------------+
| 2026-06-01T10:07:32Z | 2026-06-01T10:00:00Z | 2026-06-01T10:15:00Z |
| 2026-06-01T10:15:00Z | 2026-06-01T10:15:00Z | 2026-06-01T10:15:00Z |
+----------------------+----------------------+----------------------+

An event exactly on a boundary stays there in both results. If you need the end of a half-open window, add 15 minutes to its lower bound.