How to Efficiently Truncate Timestamps to the Nearest 5-Minute Interval in PostgreSQL?

Truncating Timestamps to the Nearest 5-Minute Boundary in Postgres

To truncate timestamps to specific intervals, Postgres offers the date_trunc function. While truncating to hours or minutes is straightforward, finding the nearest 5-minute boundary presents a challenge.

The straightforward approach involves combining date_trunc with mathematical operators to round the timestamp:

date_trunc('hour', val) + date_part('minute', val)::int / 5 * interval '5 min'

Alternatively, two other methods exist:

Going Through Epoch/Unix Time:

SELECT to_timestamp(
    floor(EXTRACT(epoch FROM ht.time) / EXTRACT(epoch FROM interval '5 min'))
    * EXTRACT(epoch FROM interval '5 min')
) FROM huge_table AS ht LIMIT 4000000

Benchmarks:

Informal benchmarks show that the straightforward approach is faster than the epoch method:

Method	Average Runtime
Straightforward	34.55 seconds
Epoch	39.49 seconds

Conclusion:

The straightforward approach is the recommended method for truncating timestamps to the nearest 5-minute boundary in Postgres due to its superior performance. Although the epoch method may be more versatile in some cases, it is not as efficient for rounding timestamps.

The above is the detailed content of How to Efficiently Truncate Timestamps to the Nearest 5-Minute Interval in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!