Date_Trunc 5 Minute Interval in Postgresql

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

I don't think there is any quicker method.

And I don't think you should be worried about the performance of the expression.

Everything else that is involved in executing your (SELECT, UPDATE, ...) statement is most probably a lot more expensive (e.g. the I/O to retrieve rows) than that date/time calculation.

postgresql date_trunc to arbitrary precision?

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

PostgreSQL - Query to show which time intervals contain data

I need a function to round a timestamp down to the previous 5 minute boundary:

CREATE OR REPLACE FUNCTION trunc_five_min(timestamp with time zone)
   RETURNS timestamp with time zone
   LANGUAGE sql STABLE AS
$$SELECT date_trunc('hour', $1)
         + date_trunc(
              'minute',
              ($1 - date_trunc('hour', $1)) / 5
           ) * 5$$;

This function will get inlined into the query, so you can see it as a notational shortcut.

When I have a time series table like this:

TABLE samples ORDER BY ts;

┌────────────────────────┐
│           ts           │
├────────────────────────┤
│ 2017-06-02 11:58:30+02 │
│ 2017-06-02 12:00:00+02 │
│ 2017-06-02 12:03:00+02 │
│ 2017-06-02 12:17:00+02 │
│ 2017-06-02 12:17:22+02 │
└────────────────────────┘
(5 rows)

I can produce the desired result with this query:

WITH lim AS (SELECT trunc_five_min(min(ts)) AS min,
                    trunc_five_min(max(ts)) AS max
             FROM samples)
SELECT i.i AS interval_start,
       count(s.ts) AS num_samples
FROM lim
   CROSS JOIN LATERAL generate_series(lim.min, lim.max, INTERVAL '5 minutes') i
   LEFT JOIN samples s
      ON s.ts >= i AND s.ts < i + INTERVAL '5 minutes'
GROUP BY i.i ORDER BY i.i;

┌────────────────────────┬─────────────┐
│     interval_start     │ num_samples │
├────────────────────────┼─────────────┤
│ 2017-06-02 11:55:00+02 │           1 │
│ 2017-06-02 12:00:00+02 │           2 │
│ 2017-06-02 12:05:00+02 │           0 │
│ 2017-06-02 12:10:00+02 │           0 │
│ 2017-06-02 12:15:00+02 │           2 │
└────────────────────────┴─────────────┘
(5 rows)

Postgres - Return 0 count for intervals with no data in date_trunc

You are close. But the key idea is that you need to use the columns from the generate_series() for the group by key:

select d.dte, count(a.activitytime)
from generate_series(current_date - interval '10 day', current_date, '5 minute') d(dte) left join
     activities a
     on a.activitytime >= d.dte and a.activitytime < d.dte + interval '5 minute'
group by d.dte 
order by d.dte;

---

Related Topics