postgresql date_trunc to arbitrary precision?

Question

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

PostgreSQL 14 will support such operation out-of-the box:https://stackoverflow.com/a/66907341/5070879 — Lukasz Szozda, Apr 01 '21 at 15:26

Alex Dvoretsky · Accepted Answer · 2019-03-23T18:36:29.300

5

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;

edited Mar 23 '19 at 18:36

answered Sep 08 '14 at 05:18

Alex Dvoretsky

938
9
21

@alex-dvortsky Thank you very much. Can you help me come up with a solution that I can use *n* as the round up unit? Say `interval 'n min'` – David S. Sep 09 '14 at 12:23
Quick update needed: the prototype of the second function is wrong: `CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) ` would be more accurate (edited the name, added missing second parameter) – Romain G Nov 22 '16 at 16:25
Thanks. Fixed signature of the function – Alex Dvoretsky Nov 23 '16 at 13:41
the second function seems to not actually work: `jasen=# select round_time_nm(now(),10); -[ RECORD 1 ]-+----------------------- round_time_nm | 2019-03-17 23:02:00+00 ` – Jasen Mar 17 '19 at 23:12
@Jasen you are right. Fixed the function. Thank you for pointing – Alex Dvoretsky Mar 23 '19 at 18:37

score 3 · Answer 2 · answered Mar 17 '19 at 23:17

here's an improved version of date_trunc

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
    RETURNS timestamptz 
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    AS $$ 
    select
        case when $2::bigint >= 0::bigint then
            $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
        else
            $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
            - (extract (epoch from $1)*1000000 ) ::bigint
        end ::timestamptz
$$;

this allows rounding to any fixed-length interval eg: '864 seconds' (divinding days into 100 parts) or '14 days' dividing the calendar into fortnights. the basis is '2000-01-01 00:00:00.0 +00' which is the epoch used to compute postgres timestamp values.

it works by coercing the timestamptz value and the interval into bigints and doing integer arithmetic on them then coercing them back to timestamps

negative inputs need special handling (the case statement) as % causes rounding towards zero.

TmTron · Answer 3 · 2021-06-02T16:57:34.180

2

Postgres 14 date_bin.
Example use

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');

Result: 2020-02-11 15:30:00

The timescaleDb extension has a time_bucket function that supports day, minutes and lower intervals.
Note: it does currently not support months, years: see #414

edited Jun 02 '21 at 16:57

answered Apr 30 '20 at 08:23

TmTron

17,012
10
94
142

postgresql date_trunc to arbitrary precision?

3 Answers3

Linked

Related