CODEWITHSUNDEEP
sqlpostgresql
davviid
davviid

Reputation: 81

custom DATE_TRUNC timeframes

I'm trying to customize the timeframe in my DATE_TRUNC function, so instead of the typical

DATE_TRUNC('hour', user.created_at)

ideally, I want something along the lines of

DATE_TRUNC('3 hours', user.created_at)

but of course that's not a valid time zone unit, so I get an error like 

ERROR:  timestamp with time zone units "3 hour" not recognized
********** Error **********

ERROR: timestamp with time zone units "3 hour" not recognized
SQL state: 22023

I know I could modify a CASE statement like 

'2019-7-26'::DATE + {{number}} * (CASE WHEN 'hour' THEN '1 hour'::INTERVAL
                                      WHEN 'minute' THEN '1 minute'::INTERVAL
                                      WHEN 'second' THEN '1 second'::INTERVAL
                                   END)

but I'm not looking to shift the timestamp and I'm not sure how to modify it to achieve what I want above.

Upvotes: 2

Views: 1822

Answers (4)

Eric M.
Eric M.

Reputation: 2967

Since PG 14, one can use date_bin:

-- Params: interval, datetime, source
SELECT date_bin('3 hours', user.created_at, date_trunc('day', user.created_at))

The actual source ensured to be aligned on 00, 03, 06, ..., 18, 21 hour.

Doc: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-BIN

Upvotes: 0

Belayer
Belayer

Reputation: 14886

I'm not sure I fully understand what your "DATE_TRUNC('3 hours', user.created_at)", but it looks like returning some interval from midnight. So I'll proceed with that assumption.
The built in date_trunc function is defined to take 2 parameters, text and timestamp (actually overloaded for timestamp with time zone and timestamp without time zone). You could extend this overload concept and with overloads taking an interval instead of text." So 

   create or replace function date_trunc (intv interval, ts timestamp without time zone)
      returns timestamp without time zone 
     language sql  immutable leakproof
    as $$
        select date_trunc('day', ts) + intv;
    $$;

    create or replace function date_trunc (intv interval, ts timestamp with time zone)
      returns timestamp with time zone
     language sql  immutable leakproof
    as $$
        select date_trunc('day', ts) + intv;
    $$;

    -- Test
    select date_trunc('3 hours'::interval, now()) with_ts
         , date_trunc('3 hours'::interval, now()::timestamp without time zone) without_ts;

However I don't like redefining built in functions, causes whole lot of confusion when something goes wrong later. So instead of overloading the built-in I would create a new user defined function name; like Date_Trunc_To_Interval. But you're free to be dangerous.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

A little painful, but you can do:

select (date_trunc('day', user.created_at) + 
        floor(extract(hour from user.created_at) / 3) * interval '3 hour'
       )

Upvotes: 1

datawolf
datawolf

Reputation: 51

You could do this: date_trunc('hour', user.created_at) + interval '3 hour'

What result are you trying to get? You said that you don't want to shift the timestamp, so what are you trying to have return with the '3 Hours'?

Upvotes: 1

Related Questions