Extract 30 minutes from timestamp and group it by 30 mins time interval -PGSQL

Question

In PostgreSQL I am extracting hour from the timestamp using below query.

select count(*) as logged_users, EXTRACT(hour from login_time::timestamp) as Hour 
from loginhistory 
where login_time::date = '2021-04-21' 
group by Hour order by Hour;

And the output is as follows

 logged_users | hour
--------------+------
           27 |    7
           82 |    8
          229 |    9
         1620 |   10
         1264 |   11
         1990 |   12
         1027 |   13
         1273 |   14
         1794 |   15
         1733 |   16
          878 |   17
          126 |   18
           21 |   19
            5 |   20
            3 |   21
            1 |   22

I want the same output for same SQL for 30 mins. Please suggest

Answer 1

SELECT to_timestamp((extract(epoch FROM login_time::timestamp)::bigint / 1800) * 1800)::timestamp AS interval_30_min
     , count(*) AS logged_users
FROM   loginhistory
WHERE  login_time::date = '2021-04-21'  -- inefficient!
GROUP  BY 1
ORDER  BY 1;

Extracting the epoch gets the number of seconds since the epoch. Integer division truncates. Multiplying back effectively rounds down, achieving the same as date_trunc() for arbitrary time intervals.
1800 because 30 minutes contain 1800 seconds.
Detailed explanation:

• Truncate timestamp to arbitrary intervals

The cast to timestamp makes me wonder about the actual data type of login_time? If it's timestamptz, the cast depends on your current time zone setting and sets you up for surprises if that setting changes. See:

• How do I match an entire day to a datetime field?
• Subtract hours from the now() function
• Ignoring time zones altogether in Rails and PostgreSQL

Depending on the actual data type, and exact definition of your date boundaries, there is a more efficient way to phrase your WHERE clause.

Answer 2

You can change the column on which you're aggregating to use the minute too:

select
  count(*) as logged_users,
  CONCAT(EXTRACT(hour from login_time::timestamp), '-', CASE WHEN EXTRACT(minute from login_time::timestamp) < 30 THEN 0 ELSE 30 END) as HalfHour 
from loginhistory 
where login_time::date = '2021-04-21' 
group by HalfHour
order by HalfHour;