Range columns in query builder

[vigneshgurusamy]

Hi,

I'm planning to migrate existing timestamp columns to tsrange using generated column like mentioned in the blog

https://blog.brackets.sk/ranges-in-laravel-7-using-postgresql/

I see tsrange() is supported for Migration but not in the query builder.

Is there any plan to add range column support in the query builder in the near future?

[tpetry]

I will add rich support for all the PostgreSQL types at some point, I just don't have an effective plan for it yet. With every method I add, there's a chance for a conflict with Laravel's own query builder methods, like the recent whereJsonContainsKey one.

But you don't have to use the raw method, you can use the normal where method like this:

$query->where('valid_range', '@>', now())

And you should better use tstzrange because timestamps without a timezone are discouraged in PostgreSQL: Don't use timestamp (without time zone)

[boris-glumpler]

@tpetry slightly off-topic here... In the wiki article you linked it also says to not use timestamp(0) or timestamptz(0), which Laravel uses by default. How would you handle this in Laravel?

Edit: Did not know that you can pass null for the precision to disable it.

[vigneshgurusamy]

@tpetry I have tried to use your suggestions with tstzrange but it fails without explicit type cast

create table vacations
(
    vacation_id     serial primary key,
    vacation_range  tstzrange
);

> DB::table('vacations')->where('vacation_range', '@>', now())->get();

Illuminate\Database\QueryException with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  malformed range literal: "2022-12-08 05:35:02"
DETAIL:  Missing left parenthesis or bracket.
CONTEXT:  unnamed portal parameter $1 = '...' (SQL: select * from "vacations" where "vacation_range" @> 2022-12-08 05:35:02)'

Raw query with explicit type cast works

DB::table('vacations')
  ->whereRaw("vacation_range @> ?::TIMESTAMPTZ", [now()])
  ->get();

Am I missing something here?

[tpetry]

The value in your first query is missing the timezone, therefore it is not working.

[vigneshgurusamy]

@tpetry I have tried with multiple combinations with timezone & without timezone data types, but no luck.

create table vacations
(
    vacation_id     serial primary key,
    vacation_range  tsrange,
    vacation_range_tz  tstzrange
);

Not working

$ts = now()->setTimezone('Asia/Kolkata');

DB::table('vacations')->where('vacation_range', '@>', $ts)->get();
DB::table('vacations')->where('vacation_range', '@>', $ts->toIso8601String())->get();
DB::table('vacations')->where('vacation_range_tz', '@>', $ts)->get();
DB::table('vacations')->where('vacation_range_tz', '@>', $ts->toIso8601String())->get();

Working

$ts = now()->setTimezone('Asia/Kolkata');

DB::table('vacations')
  ->whereRaw("vacation_range @> ?::TIMESTAMP", [$ts->toIso8601String()])
  ->get();

DB::table('vacations')
  ->whereRaw("vacation_range_tz @> ?::TIMESTAMPTZ", [$ts->toIso8601String()])
  ->get();

I feel I cannot use where() method for range data types.

[tpetry]

You are correct. The @> operator defaults to using a range value (which I use in my application), if you want to pass it a single value you need to cast the placeholder.