What Is the Fastest Way to Truncate Timestamps to 5 Minutes in Postgres

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.

Postgres: how do you round a timestamp up or down to the nearest minute?

Use the built-in function date_trunc(text, timestamp), for example:

select date_trunc('minute', now())

Edit: This truncates to the most recent minute. To get a rounded result, add 30 seconds to the timestamp first, for example:

select date_trunc('minute', now() + interval '30 second')

This returns the nearest minute.

See Postgres Date/Time Functions and Operators for more info

How to round to nearest X minutes with PL/pgSQL?

Instead of adding or subtracting

_minutes * interval '1 minute'

you should be subtracting

(_minutes % _nearest) * interval '1 minute'

or adding

(_nearest - (_minutes % _nearest)) * interval '1 minute'

How to truncate seconds from timestamp in postgres?

Either truncate the timestamp by minutes using date_trunc, which will return a timestamp without seconds, or use to_char if it is only about formatting the output:

SELECT date_trunc('minute',VISIT_DATE) FROM t;
SELECT to_char(VISIT_DATE,'yyyy-mm-dd hh24:mi') FROM t;

Demo:

Using date_trunc (result as timestamp):

SELECT date_trunc('minute','2022-01-13 11:04:15'::timestamp);

     date_trunc      
---------------------
 2022-01-13 11:04:00
(1 Zeile)

Using to_char (result as text):

SELECT to_char('2022-01-13 11:04:15'::timestamp,'yyyy-mm-dd hh24:mi');

     to_char      
------------------
 2022-01-13 11:04

Postgresql round timestamp to nearest 30 seconds

Here is one method:

select (date_trunc('minute', ts) +
        (case when extract(second from ts) > 30 then 30 else 0 end) * interval '1 second' 
       ) as rounded_ts

Note: This rounds 30 second downward.

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;

---

Related Topics