SELECT RANGE 1 IN 1 HOUR - POSTGRESQL

Asked

Viewed 145 times

1

GOAL

I’d like to make 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

EXPECTED OUTPUT

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: Reworded question

SQL FIDDLE

database postgresql
  • 1

    For your example of data and expected result, you could use Where Extract('minute' from time) = 0 that solves.

  • I’ll try it, thanks buddy!!

2 answers

1

See if this is what you want:

SELECT * FROM generate_series(
        (SELECT min(date_trunc('hour', time)) FROM t)::timestamp, 
        (SELECT max(date_trunc('hour', time)) FROM t)::timestamp, 
        '1 hour'::interval);

  • Thank you very much!!

  • 1

    Just pay attention to the nomenclature as RANGE is a data type of Postgresql. https://www.postgresql.org/docs/current/rangetypes.html

  • Whoa, I didn’t know that, thanks for the tip!

  • This query can return missing records in the table t, see only: http://www.sqlfiddle.com/#! 17/f2b34e/1

  • From what I understood he wanted the timestamps from hour to hour without the need to be records of his table, only the start and end time being the smallest and largest time existing in the table. But you may have guessed wrong.

1


You came pretty close to solving the problem, look at that:

SELECT t_id, time FROM t WHERE date_trunc('hour',time) = time;

Exit:

| t_id |                 time |
|------|----------------------|
|    1 | 2019-10-28T08:00:00Z |
|    3 | 2019-10-28T09:00:00Z |
|    5 | 2019-10-28T10:00:00Z |

See working on SQL Fiddle

  • In one line solved the problem rsrs' thank you very much!

Browser other questions tagged database postgresql

You are not signed in. Login or sign up in order to post.