PostgreSQL Range Types for Laravel 8.x to 12

Installation

composer require sunaoka/laravel-postgres-range

Features

• Range Types
  • int4range — Range of integer
  • int8range — Range of bigint
  • numrange — Range of numeric
  • tsrange — Range of timestamp without time zone
  • tstzrange — Range of timestamp with time zone
  • daterange — Range of date

Usage

Table

CREATE TABLE tests (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ts_range tsrange,
  ts_tz_range tstzrange,
  date_range daterange,
  int4_range int4range,
  int8_range int8range,
  num_range numrange,
  created_at timestamp,
  updated_at timestamp
);

Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Sunaoka\LaravelPostgres\Eloquent\Casts\DateRangeCast;
use Sunaoka\LaravelPostgres\Eloquent\Casts\Int4RangeCast;
use Sunaoka\LaravelPostgres\Eloquent\Casts\Int8RangeCast;
use Sunaoka\LaravelPostgres\Eloquent\Casts\NumRangeCast;
use Sunaoka\LaravelPostgres\Eloquent\Casts\TsRangeCast;
use Sunaoka\LaravelPostgres\Eloquent\Casts\TsTzRangeCast;

class TestModel extends Model
{
    protected $casts = [
        'ts_range' => TsRangeCast::class,
        'ts_tz_range' => TsTzRangeCast::class,
        'date_range' => DateRangeCast::class,
        'int4_range' => Int4RangeCast::class,
        'int8_range' => Int8RangeCast::class,
        'num_range' => NumRangeCast::class,
    ];
}

Range Types

use App\Models\TestModel;
use Sunaoka\LaravelPostgres\Types\Bounds\Lower;
use Sunaoka\LaravelPostgres\Types\Bounds\Upper;
use Sunaoka\LaravelPostgres\Types\DateRange;
use Sunaoka\LaravelPostgres\Types\Int4Range;
use Sunaoka\LaravelPostgres\Types\Int8Range;
use Sunaoka\LaravelPostgres\Types\NumRange;
use Sunaoka\LaravelPostgres\Types\TsRange;
use Sunaoka\LaravelPostgres\Types\TsTzRange;

$model = new TestModel();
$model->ts_range = new TsRange('2020-10-01 00:00:00', '2020-10-01 23:59:59', Lower::Inclusive, Upper::Exclusive);
$model->ts_tz_range = new TsTzRange('2020-10-01 00:00:00+00:00', '2020-10-01 23:59:59+00:00', Lower::Inclusive, Upper::Exclusive);
$model->date_range = new DateRange('2020-10-01', '2020-10-02', Lower::Inclusive, Upper::Exclusive);
$model->int4_range = new Int4Range(1, 3, Lower::Inclusive, Upper::Exclusive);
$model->int8_range = new Int8Range(1000, 3000, Lower::Inclusive, Upper::Exclusive);
$model->num_range = new NumRange(0.1, 0.3, Lower::Inclusive, Upper::Exclusive);
$model->save();

insert into "tests" (
    "ts_range",
    "ts_tz_range",
    "date_range",
    "int4_range",
    "int8_range",
    "num_range"
) values (
    '["2020-10-01 00:00:00","2020-10-01 23:59:59")',
    '["2020-10-01 00:00:00+00:00","2020-10-01 23:59:59+00:00")',
    '[2020-10-01,2020-10-02)',
    '[1,3)',
    '[1000,3000)',
    '[0.1,0.3)'
)
returning "id"

$model = TestModel::find(1);

echo $model->ts_range->lower()->format('Y-m-d H:i:s');  // lower() or from()
// => 2020-10-01 00:00:00
echo $model->ts_range->upper()->format('Y-m-d H:i:s');  // lower() or from()
// => 2020-10-01 23:59:59

echo $model->ts_tz_range->lower()->format('Y-m-d H:i:sP');  // lower() or from()
// => 2020-10-01 00:00:00+00:00
echo $model->ts_tz_range->upper()->format('Y-m-d H:i:sP');  // lower() or from()
// => 2020-10-01 23:59:59+00:00

echo $model->date_range->lower()->format('Y-m-d');  // lower() or from()
// => 2020-10-01
echo $model->date_range->upper()->format('Y-m-d');  // lower() or from()
// => 2020-10-02

echo $model->int4_range->lower();  // lower() or from()
// => 1
echo $model->int4_range->upper();  // lower() or from()
// => 3

echo $model->int8_range->lower();  // lower() or from()
// => 1000
echo $model->int8_range->upper();  // lower() or from()
// => 3000

echo $model->num_range->lower();  // lower() or from()
// => 0.1
echo $model->num_range->upper();  // lower() or from()
// => 0.3