CODEWITHSUNDEEP
databasepostgresql
Luis Henrique
Luis Henrique

Reputation: 771

SELECT RANGE 1 IN 1 HOUR - POSTGRESQL

GOAL

I would like to do a SELECT within a range of 1 hour in 1 hour

DATA TABLE

CREATE TABLE t (
    t_id   INT PRIMARY KEY,
    time   TIMESTAMP
);
INSERT INTO t VALUES (1, '2019-10-28 08:00:00');
INSERT INTO t VALUES (2, '2019-10-28 08:30:00');
INSERT INTO t VALUES (3, '2019-10-28 09:00:00');
INSERT INTO t VALUES (4, '2019-10-28 09:30:00');
INSERT INTO t VALUES (5, '2019-10-28 10:00:00');
INSERT INTO t VALUES (6, '2019-10-28 10:30:00');

QUERY

SELECT
    t_id,
    MIN(time)
FROM
    t
WHERE
    date_trunc('day', time) = current_date
GROUP BY
    t_id,
    date_trunc('hour', time)

OUTPUT

t_id    min
4   2019-10-28 09:30:00
5   2019-10-28 10:30:00
1   2019-10-28 08:00:00
2   2019-10-28 08:30:00
3   2019-10-28 09:00:00
6   2019-10-28 10:00:00

OUTPUT ESPERADO

t_id    min
1   2019-10-28 08:00:00
3   2019-10-28 09:00:00
5   2019-10-28 10:00:00

NOTE: REFORMED QUESTION

SQL FIDDLE

Upvotes: 0

Views: 70

Answers (1)

Jeremy
Jeremy

Reputation: 6713

You can use DISTINCT ON to get the first t_id per hour.

SELECT DISTINCT ON (date_trunc('hour', time))
t_id, date_trunc('hour', time)
FROM t
WHERE date_trunc('day', time) = current_date
ORDER BY date_trunc('hour', time), t_id;

DISTINCT ON (date_trunc('hour', time)) along with ORDER BY date_trunc('hour', time), t_id allows us to grab the lowest t_id for each hour.

If instead you want the very first t_id in order of time, you could do the following:

SELECT DISTINCT ON (date_trunc('hour', time))
t_id, date_trunc('hour', time)
FROM t
WHERE date_trunc('day', time) = current_date
ORDER BY date_trunc('hour', time), time;

In this case, they are the same, but in the fiddle you added to the comments, they are not.

Upvotes: 1

Related Questions