# Available range types
# daterange
Let's imagine we want to use daterange column in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->dateRange('date_range');
// you can add any modifications
// $table->dateRange('date_range')->nullable();
// $table->dateRange('date_range')->default('[2010-01-01,2010-01-02)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\DateRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'date_range' => DateRangeCast::class,
];
// ...
}
Now, whenever we access date_range attribute in our model,
it will return Belamov\PostgresRange\Ranges\DateRange instance
# DateRange object
WARNING
DateRange object is automatically canonicalizable
Initialization:
use Belamov\PostgresRange\Ranges\DateRange;
$range = new DateRange('2010-01-10', '2010-01-15', '[', ')');
TIP
Note that you can initialize DateRange object either with strings,
or with any objects that implement DateTime interface
for example with Carbonobjects
$range = new DateRange(Carbon::parse('2010-01-10'), Carbon::now(), '[', ')')
API:
$range->from(); // CarbonImmutable object
$range->to(); // CarbonImmutable object
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [2010-01-10,2010-01-15)
$range->forSql(); // '[2010-01-10,2010-01-15)'::daterange
Updating or creating model:
$model->update(['date_range' => $range]);
$model->date_range; // DateRange object
$model->date_range->from(); // CarbonImmutable object
$model->date_range->to(); // CarbonImmutable object
# tsrange
Let's imagine we want to use tsrange column in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->timestampRange('timestamp_range');
// you can add any modifications
// $table->timestampRange('timestamp_range')->nullable();
// $table->timestampRange('timestamp_range')->default('[2010-01-01 14:30:30,2010-01-02 14:30:30)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\TimestampRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'timestamp_range' => TimestampRangeCast::class,
];
// ...
}
Now, whenever we access timestamp_range attribute in our model, it will return Belamov\PostgresRange\Ranges\TimestampRange instance
# TimestampRange object
Initialization:
use Belamov\PostgresRange\Ranges\TimestampRange;
$range = new TimestampRange('2010-01-01 14:30:30', '2010-01-02 14:30:30', '[', ')');
TIP
Note that you can initialize TimestampRange object either with strings, or with any objects that implement DateTime interface for example with Carbon objects
$range = new TimestampRange(Carbon::parse('2010-01-01 14:30:30'), Carbon::now(), '[', ')')
API:
$range->from(); // CarbonImmutable object
$range->to(); // CarbonImmutable object
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [2010-01-01 14:30:30,2010-01-02 14:30:30)
$range->forSql(); // '[2010-01-01 14:30:30,2010-01-02 14:30:30)'::tsrange
Model updating or creating:
$model->update(['timestamp_range' => $range]);
$model->timestamp_range; // TimestampRange object
$model->timestamp_range->from(); // CarbonImmutable object
$model->timestamp_range->to(); // CarbonImmutable object
# tstzrange
Let's imagine we want to use tstzrange column in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->timestampTzRange('timestamptz_range');
// you can add any modifications
// $table->timestampTzRange('timestamptz_range')->nullable();
// $table->timestampTzRange('timestamptz_range')->default('[2010-01-01 14:30:30+2:00,2010-01-02 14:30:30+2:00)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\TimestampTzRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'timestamptz_range' => TimestampTzRangeCast::class,
];
// ...
}
Now, whenever we access timestamptz_range attribute in our model, it will return Belamov\PostgresRange\Ranges\TimestampTzRange instance
WARNING
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.
You can read more about this at Documentation
Also this article will help you to figure out differences between timestamps with and without timestamps Understanding PostgreSQL Timestamp Data Types
# TimestampTzRange object
Initialization:
use Belamov\PostgresRange\Ranges\TimestampTzRange;
$range = new TimestampTzRange('2010-01-01 14:30:30+2:00', '2010-01-02 14:30:30+2:00', '[', ')');
TIP
Note that you can initialize TimestampTzRange object either with strings, or with any objects that implement DateTime interface for example with Carbon objects
$range = new TimestampTzRange(Carbon::parse('2010-01-01 14:30:30'), Carbon::now(), '[', ')')
API:
$range->from(); // CarbonImmutable object
$range->to(); // CarbonImmutable object
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [2010-01-01 14:30:30+00,2010-01-02 14:30:30+00)
$range->forSql(); // '[2010-01-01 14:30:30+00,2010-01-02 14:30:30+00)'::tstzrange
Model updating or creating:
$model->update(['timestamptz_range' => $range]);
$model->timestamptz_range; // TimestampTzRange object
$model->timestamptz_range->from(); // CarbonImmutable object
$model->timestamptz_range->to(); // CarbonImmutable object
# numrange
Let's imagine we want to use numrange column in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->floatRange('float_range');
// you can add any modifications
// $table->timestampRange('float_range')->nullable();
// $table->timestampRange('float_range')->default('[1.5,2.5)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\FloatRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'float_range' => FloatRangeCast::class,
];
// ...
}
Now, whenever we access float_range attribute in our model, it will return Belamov\PostgresRange\Ranges\FloatRange instance
# FloatRange object
Initialization:
use Belamov\PostgresRange\Ranges\FloatRange;
$range = new FloatRange(1.5, 2.5, '[', ')');
API:
$range->from(); // 1.5
$range->to(); // 2.5
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [1.5,2.5)
$range->forSql(); // '[1.5,2.5)'::numrange
Model updating or creating:
$model->update(['float_range' => $range]);
$model->float_range; // FloatRange object
$model->float_range->from(); // 1.5
$model->float_range->to(); // 2.5
# intrange
Let's imagine we want to use int4range or int8range column in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->integerRange('integer_range'); //for int4range
$table->bigIntegerRange('integer_range'); //for int8range
// you can add any modifications
// $table->integerRange('integer_range')->nullable();
// $table->integerRange('integer_range')->default('[10,20)');
// $table->bigIntegerRange('integer_range')->nullable();
// $table->bigIntegerRange('integer_range')->default('[10,20)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\IntegerRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'integer_range' => IntegerRangeCast::class,
];
// ...
}
Now, whenever we access integer_range attribute in our model, it will return Belamov\PostgresRange\Ranges\IntegerRange instance
# IntegerRange object
WARNING
IntegerRange object is automatically canonicalizable
Initialization:
use Belamov\PostgresRange\Ranges\IntegerRange;
$range = new IntegerRange(10, 20, '[', ')');
API:
$range->from(); // 10
$range->to(); // 20
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [10,20)
$range->forSql(); // '[10,20)'
Model updating or creating:
$model->update(['integer_range' => $range]);
$model->integer_range; // IntegerRange object
$model->integer_range->from(); // 10
$model->integer_range->to(); // 20
# timerange
Postgres doesn't support timerange type, so this package will define it.
Type definition happens whenever you call $table->timeRange(); in your migration files.
But all sql scripts are idempotent, so this scripts may be run multiple times.
Type definition implemented in this way:
CREATE OR REPLACE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'timerange') THEN
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_subtype_diff
);
END IF;
END$$
Type name and comparing function name are configurable:
- with env variables:
TIMERANGE_TYPENAME=timerange TIMERANGE_SUBTYPE_DIFF_FUNCTION_NAME=time_subtype_diff - or with config file:in
php artisan vendor:publish --provider="Belamov\PostgresRange\PostgresRangeServiceProvider" --tag="config"config/postges-range.php:return [ 'timerange_typename' => 'timerange', 'timerange_subtype_diff_function_name' => 'time_subtype_diff', ];
Let's imagine we want to use that custom defined timerange type in our Laravel application.
# Migrations
First, let's add it in our migration files.
public function up(): void
{
Schema::create(
'table',
static function (Blueprint $table) {
$table->id();
// ...
$table->timeRange('time_range');
// you can add any modifications
// $table->timeRange('time_range')->nullable();
// $table->timeRange('time_range')->default('[14:30:30,15:30:30)');
}
);
}
# Model property casting
Next, we should define cast for this column in our model class.
use Belamov\PostgresRange\Casts\TimeRangeCast;
class SomeModel extends Model
{
// ...
protected $casts = [
'time_range' => TimeRangeCast::class,
];
// ...
}
Now, whenever we access time_range attribute in our model, it will return Belamov\PostgresRange\Ranges\TimeRange instance
# TimeRange object
Initialization:
use Belamov\PostgresRange\Ranges\TimeRange;
$range = new TimeRange('14:30:30', '15:30:30', '[', ')');
TIP
Note that you can initialize TimeRange object either with strings,
or with any objects that implement DateTime interface
for example with Carbon objects
$range = new TimestampRange(Carbon::parse('14:30:30'), Carbon::now(), '[', ')')
API:
$range->from(); // 14:30:30
$range->to(); // 15:30:30
$range->hasUpperBoundary(); // bool
$range->hasLowerBoundary(); // bool
(string) $range; // [14:30:30,15:30:30)
$range->forSql(); // '[14:30:30,15:30:30)'::timerange
// (if you haven't change timerange typename in config)
Model updating or creating:
$model->update(['time_range' => $range]);
$model->time_range; // TimeRange object
$model->time_range->from(); // '14:30:30'
$model->time_range->to(); // '15:30:30'
# Canonicalizable ranges
The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [). (More in official documentation)
So to reflect this behaviour DateRange and IntegerRange object will be canonialized during initialization like so:
When lower boundary is exclusive:
$range = new IntegerRange(10, 20, '(', ')');
$range->from(); // 11
$range->to(); // 20
(string) $range; // '[11,20)'
$range = new DateRange($from, $to, '(', ')');
$range->from(); // equals $from->addDay()
$range->to(); // equals $to
(string) $range; // '[2010-01-11,2010-01-15)'
When upper boundary is inclusive:
$range = new IntegerRange(10, 20, '[', ']');
$range->from(); // 10
$range->to(); // 21
(string) $range; // '[10,21)'
$range = new DateRange($from, $to, '[', ']');
$range->from(); // equals $from
$range->to(); // equals $to->addDay()
(string) $range; // '[2010-01-10,2010-01-16)'