Postgresql – Compare an hour between two hour columns

operatorpostgresqltime

Please help. I am kinda new to this database world. I am using PostgreSQL.

I have a table with defined shifts like this:

CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
FROM ( VALUES 
  ( 1,'06:00:01','14:00:00' ),
  ( 2,'14:00:01','22:00:00' ),
  ( 3,'22:00:01','06:00:00' )
) AS t(id,start_hour,end_hour);

I have been using the between operator to compare a given hour with the start_hour and end_hour so I can get the corresponding shift, like this

SELECT * from Shifts S where given_hour BETWEEN start_hour AND end_hour;

It works when it is shift 1 or 2 but not with shift 3. It returns no value. Can you please give me a little advice on how should i do this?
Thank you!

Best Answer

You cannot have an interval defined by an "end" smaller than your "start". Your shift 3 does not work because you're crossing day boundaries, and your "end" (6 o'clock, is smaller than your end 22 o'clock).

If you want to define your shifts by hours, you would need to have, in general, 2 intervals per shift, and shift 3 should have:

+----+--------------+------------+--------------+------------+
| id | start_hour_1 | end_hour_1 | start_hour_2 | end_hour_2 |
+----+--------------+------------+--------------+------------+
|  1 |     06:00:00 |   14:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  2 |     14:00:00 |   22:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  3 |     22:00:00 |   24:00:00 |    00:00:00  |   06:00:00 |
+----+--------------+------------+--------------+------------+

And you should rewrite your query with:

SELECT 
    * 
FROM 
    Shifts S 
WHERE
       (given_hour >= start_hour_1 AND given_hour < end_hour_1)
    OR (given_hour >= start_hour_2 AND given_hour < end_hour_2) ;

As a little advice, I would use BETWEEN .. AND .. only with discrete variables (basically integers and texts). With continuous variables (and time is continuous), it's safer to include one of the ends and exclude the other (>= AND <): given_hour >= start_time AND given_hour < end_time. That's why I changed :01 by :00 to work this way (and avoid problems with 14:00:00.123, which could eventually happen).

Related Solutions

Postgresql – Optimizing queries on a range of timestamps (two columns)

For Postgres 9.1 or later:

CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);

In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:

PostgreSQL index not used for query on range

Consider your query:

SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

Sort order of the first column id_phi in the index is irrelevant. Since it's checked for equality (=), it should come first. You got that right. More in this related answer:

Multicolumn index and performance

Postgres can jump to id_phi = 0 in next to no time and consider the following two columns of the matching index. These are queried with range conditions of inverted sort order (<=, >=). In my index, qualifying rows come first. Should be the fastest possible way with a B-Tree index¹:

You want start_date_time <= something: index has the earliest timestamp first.
If it qualifies, also check column 3.
Recurse until the first row fails to qualify (super fast).
You want end_date_time >= something: index has the latest timestamp first.
If it qualifies, keep fetching rows until the first one doesn't (super fast).
Continue with next value for column 2 ..

Postgres can either scan forward or backward. The way you had the index, it has to read all rows matching on the first two columns and then filter on the third. Be sure to read the chapter Indexes and ORDER BY in the manual. It fits your question pretty well.

How many rows match on the first two columns?
Only few with a start_date_time close to the start of the time range of the table. But almost all rows with id_phi = 0 at the chronological end of the table! So performance deteriorates with later start times.

Planner estimates

The planner estimates rows=62682 for your example query. Of those, none qualify (rows=0). You might get better estimates if you increase the statistics target for the table. For 2.000.000 rows ...

ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

... might pay. Or even higher. More in this related answer:

Check statistics targets in PostgreSQL

I guess you don't need that for id_phi (only few distinct values, evenly distributed), but for the timestamps (lots of distinct values, unevenly distributed).
I also don't think it matters much with the improved index.

`CLUSTER` / pg_repack / pg_squeeze

If you want it faster, yet, you could streamline the physical order of rows in your table. If you can afford to lock your table exclusively (at off hours for instance), rewrite your table and order rows according to the index with CLUSTER:

CLUSTER time_limits USING idx_time_limits_inversed;

Or consider pg_repack or the later pg_squeeze, which can do the same without exclusive lock on the table.

Either way, the effect is that fewer blocks need to be read from the table and everything is pre-sorted. It's a one-time effect deteriorating over time with writes on the table fragmenting the physical sort order.

GiST index in Postgres 9.2+

¹ With pg 9.2+ there is another, possibly faster option: a GiST index for a range column.

There are built-in range types for timestamp and timestamp with time zone: tsrange, tstzrange. A btree index is typically faster for an additional integer column like id_phi. Smaller and cheaper to maintain, too. But the query will probably still be faster overall with the combined index.
Change your table definition or use an expression index.
For the multicolumn GiST index at hand you also need the additional module btree_gist installed (once per database) which provides the operator classes to include an integer.

The trifecta! A multicolumn functional GiST index:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

Use the "contains range" operator @> in your query now:

SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

SP-GiST index in Postgres 9.3+

An SP-GiST index might be even faster for this kind of query - except that, quoting the manual:

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

Still true in Postgres 12.
You would have to combine an spgist index on just (tsrange(...)) with a second btree index on (id_phi). With the added overhead, I'm not sure this can compete.
Related answer with a benchmark for just a tsrange column:

Perform this hours of operation query in PostgreSQL

MySQL intersection on one table with time type

It seems you need a self join with a non-equality condition that checks for overlaping ranges. The output can be in different formats. Depending on your needs, the easiest would be a 2-column output:

SELECT a.id AS a_id, b.id AS b_id
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
ORDER BY a.id, b.id ;

Output would be (2 columns):

#1    #2   (the whole day shares the times with morning) 
#1    #3   (the whole day shares the times with afternoon) 
#2    #1   (the morning shares the times with the whole day) 
#3    #1   (the afternoon shares the times with the whole day)

To get the rows combined, you can use GROUP_CONCAT():

SELECT a.id AS a_id, 
       GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ' ') AS b_ids
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
GROUP BY a.id
ORDER BY a.id ;

Output (2-columns):

#1    #2 #3   (the whole day shares the times with morning, afternoon) 
#2    #1      (the morning shares the times with the whole day) 
#3    #1      (the afternoon shares the times with the whole day)

If you want one row per a.id and multiple columns, you'll need dynamic SQL.