CODEWITHSUNDEEP
sqlpostgresqldatetime
M N
M N

Reputation: 96

How to Group Data by Time Period in PostgreSQL?

I need to get the count of records using PostgreSQL from time 7:00:00 am till next day 6:59:59 am and the count resets again from 7:00am to 6:59:59 am.

Where I am using backend as java (Spring boot).

The columns in my table are

How do I give the condition for shift wise?

Upvotes: 1

Views: 950

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use aggregation -- by subtracting 7 hours:

select (createdon - interval '7 hour')::date as dy, count(*)
from t
group by dy
order by dy;

Upvotes: 0

bignose
bignose

Reputation: 32309

As I understand the question, you need to have a separate group for values in each 24-hour period that starts at 07:00:00.

SELECT
    (
        date_trunc('day', (createdon - '7h'::interval))
            + '7h'::interval
    ) AS date_bucket,
    count(id) AS count
FROM lorem
GROUP BY date_bucket
ORDER BY date_bucket

This uses the date and time functions and the GROUP BY clause:

  • Shift the timestamp value back 7 hours ((createdon - '7h'::interval)), so the distinction can be made by a change of date (at 00:00:00). Then,

  • Truncate the value to the date (date_trunc('day', …)), so that all values in a bucket are flattened to a single value (the date at midnight). Then,

  • Add 7 hours again to the value (… + '7h'::interval), so that it represents the starting time of the bucket. Then,

  • Group by that value (GROUP BY date_bucket).

A more complete example, with schema and data:

DROP TABLE IF EXISTS lorem;
CREATE TABLE lorem (
    id serial PRIMARY KEY,
    createdon timestamp not null
);

INSERT INTO lorem (createdon) (
    SELECT
        generate_series(
            CURRENT_TIMESTAMP - '36h'::interval,
            CURRENT_TIMESTAMP + '36h'::interval,
           '45m'::interval)
);

Now the query:

SELECT
    (
        date_trunc('day', (createdon - '7h'::interval))
            + '7h'::interval
    ) AS date_bucket,
    count(id) AS count
FROM lorem
GROUP BY date_bucket
ORDER BY date_bucket
;

produces this result:

     date_bucket     | count 
---------------------+-------
 2019-03-06 07:00:00 |    17
 2019-03-07 07:00:00 |    32
 2019-03-08 07:00:00 |    32
 2019-03-09 07:00:00 |    16
(4 rows)

Upvotes: 1

Ancoron
Ancoron

Reputation: 2733

You'd need to pick a slice based on the current time-of-day (I am assuming this to be some kind of counter which will be auto-refreshed in some application).

One way to do that is using time ranges:

SELECT COUNT(*)
FROM mytable
WHERE createdon <@ (
    SELECT CASE
        WHEN current_time < '07:00'::time THEN
            tsrange(CURRENT_DATE - '1d'::interval + '07:00'::time, CURRENT_DATE + '07:00'::time, '[)')
        ELSE
            tsrange(CURRENT_DATE + '07:00'::time, CURRENT_DATE + '1d'::interval + '07:00'::time, '[)')
        END
)
;

Example with data: https://rextester.com/LGIJ9639

Upvotes: 2

Related Questions