CODEWITHSUNDEEP
sqlpostgresql
Linell
Linell

Reputation: 749

Check if timerange covers now() in Postgres

I have a column named office_hours with the datatype of timerange, which was defined via: 

create type timerange as range (subtype = time)

I want to write a query that checks whether or not now() is included in that time range. What I'm looking for is something similar to:

select now() <@ office_hours from users where office_hours is not null;

Whenever I try to run that query I get this error message:

dbeaver error message

What is the right way to query whether or not a timerange contains now()?

Upvotes: 2

Views: 288

Answers (1)

GMB
GMB

Reputation: 222422

The error message was giving a good hint already. Since you have a create a time range, you would need to cast now() to time before checking for containment.

Consider:

select now()::time <@ office_hours from users where office_hours is not null;

Demo on DB Fiddle:

create type timerange as range (subtype = time);
select now(), now()::time <@ timerange('[20:00,23:00]') contained;

Yields:

| now                      | contained |
| ------------------------ | --------- |
| 2019-04-05T20:54:13.113Z | true      |

Upvotes: 1

Related Questions