How correctly test the date is within time intervals

Question

I have a timestamp of an user action. And several time intervals when the user have grants to perform the action. I need to check wether the timestamp of this action is within at least one of the time intervals or not.

Table with users:

CREATE TABLE ausers (
    id serial PRIMARY KEY,
    user_name VARCHAR(255) default NULL,
    action_date TIMESTAMP
);
INSERT INTO ausers VALUES(1,'Jhon', '2018-02-21 15:05:06');
INSERT INTO ausers VALUES(2,'Bob', '2018-05-24 12:22:26');

#|id|user_name|action_date
----------------------------------
1|1 |Jhon     |21.02.2018 15:05:06
2|2 |Bob      |24.05.2018 12:22:26

Table with grants:

CREATE TABLE user_grants (
    id serial PRIMARY KEY,
    user_id INTEGER,
    start_date TIMESTAMP,
    end_date TIMESTAMP
);
INSERT INTO user_grants VALUES(1, 1, '2018-01-01 00:00:01', '2018-03-01 00:00:00');
INSERT INTO user_grants VALUES(2, 1, '2018-06-01 00:00:01', '2018-09-01 00:00:00');
INSERT INTO user_grants VALUES(3, 2, '2018-01-01 00:00:01', '2018-02-01 00:00:00');
INSERT INTO user_grants VALUES(4, 2, '2018-02-01 00:00:01', '2018-03-01 00:00:00');

#|id|user_id|start_date           |end_date
------------------------------------------------------
1|1 |1      |01.01.2018 00:00:01  |01.03.2018 00:00:00
2|2 |1      |01.06.2018 00:00:01  |01.09.2018 00:00:00
3|3 |2      |01.01.2018 00:00:01  |01.02.2018 00:00:00
4|4 |2      |01.02.2018 00:00:01  |01.03.2018 00:00:00

The query:

select u.user_name,
case 
    when array_agg(gr.range) @> array_agg(tstzrange(u.action_date, u.action_date, '[]')) then 'Yes'
    else 'No' 
end as "permition was granted"
from ausers u
left join (select tstzrange(ug.start_date, ug.end_date, '[]') as range, ug.user_id as uid 
from user_grants ug) as gr on gr.uid = u.id
group by u.user_name;

Result:

#|user_name|permition was granted
---------------------------------
1|Bob      |No
2|Jhon     |No

Timestamp '01.02.2018 15:05:06' is within "01.01.2018 00:00:01, 01.03.2018 00:00:00" range, so "Bob" had grants to perform action and where should be "Yes" in first row, not "No".

The expected output is like this:

#|user_name|permition was granted
---------------------------------
1|Bob      |Yes
2|Jhon     |No

I tried to test like this:

select array_agg(tstzrange('2018-02-21 15:05:06', '2018-02-21 15:05:06', '[]')) <@ array_agg(tstzrange('2018-01-01 00:00:01', '2018-03-01 00:00:01', '[]'));

#|?column?
----------
 |false

Result is "false". But if remove array_agg function

select tstzrange('2018-02-21 15:05:06', '2018-02-21 15:05:06', '[]') <@ tstzrange('2018-01-01 00:00:01', '2018-03-01 00:00:01', '[]');

#|?column?
----------
 |true

It works fine - the result is "true". Why? Whats's wrong with array_agg?

I have to use array_agg because I have several time intervals to compare.

I have to make "fake" time interval

array_agg(tstzrange(u.action_date, u.action_date, '[]'))

from one timestamp because operator @> doesn't allow to compare the timestamp and array of timestamps intervals. How to compare that one date is within at least on time interval from the array of time intervals?

score 1 · Accepted Answer · answered Oct 25 '19 at 15:03

There are several @> operators in PostgreSQL:

tstzrange @> tstzrange tests if the first interval contains the second
anyarray @> anyarray tests if the first array contains all elements of the second array.

In your query that will test if for each interval in the second array there is an equal interval in the first array.

Therebis a way to test if an interval is contained in one of the elements of an array of intervals:

someinterval <@ ANY (array_of_intervals)

but there is no straightforward way to express your condition with an operator.

Do without an aggregate, join the two tables on @> and count the result rows.

Thank you very much. Very nice idea to count the number of times the timestamp is within the time interval. — PavelP, Oct 25 '19 at 17:49

score 1 · Answer 2 · answered Oct 25 '19 at 17:56

Since the all three dates aare scalar quantities Postgres range checking is not required, a simple BETWEEN operation suffices.

select au.user_name
     , case when ug.user_id is null then 'No' else 'Yes' end authorized 
  from ausers au
  left join user_grants ug   
         on (    au.id = ug.id
             and au.action_date between ug.start_date and ug.end_date
            );

BTW. I think your expected results posted are backwards. Neither user name has a timestamp of '01.02.2018 15:05:06' as indicated in the description.

How correctly test the date is within time intervals

2 Answers2