Truncate timestamp to arbitrary intervals

by

Consider this demo to bring timestamps down to a resolution of 15 minutes and aggregate resulting dupes:

WITH tbl(id, ts) AS ( VALUES
    (1::int, '2012-10-04 00:00:00'::timestamp)
   ,(2, '2012-10-04 18:23:01')
   ,(3, '2012-10-04 18:30:00')
   ,(4, '2012-10-04 18:52:33')
   ,(5, '2012-10-04 18:55:01')
   ,(6, '2012-10-04 18:59:59')
   ,(7, '2012-10-05 11:01:01')
   )
SELECT to_timestamp((extract(epoch FROM ts)::bigint / 900)*900)::timestamp
                                                            AS lower_bound
     , to_timestamp(avg(extract(epoch FROM ts)))::timestamp AS avg_ts
     , count(*) AS ct
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Result:

     lower_bound     |       avg_ts        | ct
---------------------+---------------------+----
 2012-10-04 00:00:00 | 2012-10-04 00:00:00 |  1
 2012-10-04 18:15:00 | 2012-10-04 18:23:01 |  1
 2012-10-04 18:30:00 | 2012-10-04 18:30:00 |  1
 2012-10-04 18:45:00 | 2012-10-04 18:55:51 |  3
 2012-10-05 11:00:00 | 2012-10-05 11:01:01 |  1

The trick is to extract a unix epoch like @Michael already posted. Integer division lumps them together in buckets of the chosen resolution, because fractional digits are truncated.

I divide by 900, because 15 minutes = 900 seconds.

Multiply by the same number to get the resulting lower_bound.
Convert the unix epoch back to a timestamp with to_timestamp().

This works great for intervals that can be represented without fractional digits in the decimal system. For even more versatility use the often overlooked function width_bucket() like I demonstrate in this recent, closely related answer. More explanation, links and an sqlfiddle demo over there.

More Related Contents:

  1. PostgreSQL: running count of rows for a query ‘by minute’
  2. Aggregate columns with additional (distinct) filters
  3. Two SQL LEFT JOINS produce incorrect result
  4. Create a pivot table with PostgreSQL
  5. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  6. Return multiple columns of the same row as JSON array of objects
  7. How to include “zero” / “0” results in COUNT aggregate?
  8. Time zone storage in data type “timestamp with time zone”
  9. How do I determine the last day of the previous month using PostgreSQL?
  10. GROUP BY + CASE statement
  11. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  12. Best way to count rows by arbitrary time intervals
  13. How do you do date math that ignores the year?
  14. Selecting data into a Postgres array
  15. Compute percents from SUM() in the same SELECT sql query
  16. Get the distinct sum of a joined table column
  17. Aggregate a single column in query with many columns
  18. Multiple array_agg() calls in a single query
  19. Postgres window function and group by exception
  20. Count cumulative total in Postgresql
  21. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  22. Subtract hours from the now() function
  23. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  24. Sort by column ASC, but NULL values first?
  25. Using an Alias column in the where clause in Postgresql
  26. postgresql generate sequence with no gap
  27. PostgreSQL: Case insensitive string comparison
  28. Custom aggregate function (concat) in SQL Server
  29. Create date from day, month, year fields in MySQL
  30. Postgres trigger after insert accessing NEW

Leave a Comment