What is the fastest way to truncate timestamps to 5 minutes in Postgres?

by

I was wondering the same thing. I found two alternative ways of doing this, but the one you suggested was faster.

I informally benchmarked against one of our larger tables. I limited the query to the first 4 million rows. I alternated between the two queries in order to avoid giving one a unfair advantage due to db caching.

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

(Note this produces timestamptzeven if you used a time zone unaware datatype)

Results

  • Run 1: 39.368 seconds
  • Run 3: 39.526 seconds
  • Run 5: 39.883 seconds

Using date_trunc and date_part

SELECT 
    date_trunc('hour', ht.time) 
    + date_part('minute', ht.time)::int / 5 * interval '5 min'
FROM huge_table AS ht LIMIT 4000000

Results

  • Run 2: 34.189 seconds
  • Run 4: 37.028 seconds
  • Run 6: 32.397 seconds

System

  • DB version: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
  • Cores: Intel® Xeon®, E5-1650v2, Hexa-Core
  • RAM: 64 GB, DDR3 ECC RAM

Conclusion

Your version seems to be faster. But not fast enough for my specific use case. The advantage of not having to specify the hour makes the epoch version more versatile and produces simpler parameterization in client side code. It handles 2 hour intervals just as well as 5 minute intervals without having to bump the date_trunc time unit argument up. On a end note, I wish this time unit argument was changed to a time interval argument instead.

More Related Contents:

  1. Best way to select random rows PostgreSQL
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Optimise PostgreSQL for fast testing
  4. Normalize array subscripts so they start with 1
  5. SQL SELECT speed int vs varchar
  6. Time zone storage in data type “timestamp with time zone”
  7. How do I determine the last day of the previous month using PostgreSQL?
  8. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  9. How do you do date math that ignores the year?
  10. Get execution time of PostgreSQL query
  11. Subtract hours from the now() function
  12. Truncate timestamp to arbitrary intervals
  13. Count(*) vs Count(1) – SQL Server
  14. Does PostgreSQL support “accent insensitive” collations?
  15. Split comma separated column data into additional columns
  16. Is a view faster than a simple query?
  17. How to convert DateTime to VarChar
  18. How to delete duplicate entries?
  19. Does Foreign Key improve query performance?
  20. SQL Server convert string to datetime
  21. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  22. Error: Column does not exist
  23. DROP FUNCTION without knowing the number/type of parameters?
  24. Getting Dates between a range of dates
  25. SQL Server 2008 paging methods?
  26. How do I ALTER a PostgreSQL table and make a column unique?
  27. Find records where join doesn’t exist
  28. Rails 4 LIKE query – ActiveRecord adds quotes
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment