Postgresql work with time ranges, not datetime ranges

Question

Please advise how I can achieve simplicity working with time ranges in Postgresql. I want to perform queries like:

SELECT user_id 
FROM user_logs 
WHERE login_time_range BETWEEN '20:00' AND '02:00'

Note, I am not looking for datetime type. I am also aware of tsrange which does not suit my needs neither.

In the query above i want to store login_time_range as a range of starting and ending hour.

For example: [01:00, 02:30)

Later I wanna query to check weather login_time_range is falling into the range of the WHERE clause.

user330315 · Accepted Answer · 2021-02-03 15:46:26Z

2

Just create your own timerange type:

create type timerange as range (subtype = time);

create table user_logs
(
  ..., 
  login_time_range timerange
);

Then you can use all range type operators on that column.

However, that will not properly deal with ranges that cross midnight like in your example 20:00 - 02:00.

A better way might be to use two columns: a start_time and a duration:

create table user_logs
(
  ..., 
  login_time time,
  login_duration interval
);

Then you can use the current date (or which ever you want) to detect the "midnight crossing:

select current_date + login_time as login_start_time, 
       current_date + login_time + duration as login_end_time

answered Feb 3, 2021 at 15:46

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1