sqlpostgresqlrange-types

How to return all entries "from the current year"?

I have a tsrange column named time_range. I want to get all rows from my user from the current year. So when a row is like this:

("2024-01-01, 2025-02-02")

I also want it because 2024 is included. With a normal timestamp column I know how to do it but with tsrange I have no idea how that works.

My current query:

SELECT time_range
FROM holidays
WHERE user_id = 1
AND time_range <@ tsrange(start_time, end_time, '()')

Result:

("2024-02-02", "2024-04-05")
("2024-12-02", "2025-01-02")
("2025-03-03", "2025-04-04")

I want to all rows where any part of 2024 is included. So the first and second one, but not the third.

Solution

  • You want the "overlap" operator && for range types.

    To get all entries where time_range overlaps with the year 2024 in any way:

    SELECT time_range
FROM   holidays
WHERE  user_id = 1
AND    time_range && '[2024-1-1, 2025-1-1)';  -- tsrange type

    Note how the lower bound in my range is included ([), but upper bound is excluded ()).
    Related:

    Aside: If there is a remote chance that different time zones might be involved, you'll want to use tstzrange instead of tsrange ...