|
|
|
> I hope it's not only me that can take `start_time` and `finish_time` **ANYMORE!!!**
|
|
|
|
|
|
|
|
Date or time ranges features. This provides extended and complex calculations over date and time ranges. In a few words, you can now store `start_time` and `finish_time` in the same column and relies on the methods provided here to fo your magic. [PostgreSQL Docs](https://www.postgresql.org/docs/9.6/functions-range.html)
|
|
|
|
|
|
|
|
# How it works
|
|
|
|
|
|
|
|
### Migration
|
|
|
|
|
|
|
|
Create a table with the single column set as any type of time/date range (`daterange`, `tsrange`, or `tstzrange`), like:
|
|
|
|
```ruby
|
|
|
|
create_table :events do |t|
|
|
|
|
t.string :name
|
|
|
|
t.tsrange :period
|
|
|
|
t.interval :interval
|
|
|
|
end
|
|
|
|
```
|
|
|
|
|
|
|
|
### Models
|
|
|
|
|
|
|
|
You have to go to each of your models and enable the functionality for each range-type field. The method name is defined on [`period.base_method`](https://github.com/crashtech/torque-postgresql/wiki/Configuring#period.base_method).
|
|
|
|
```ruby
|
|
|
|
# models/event.rb
|
|
|
|
class Event < ActiveRecord::Base
|
|
|
|
period_for :period
|
|
|
|
end
|
|
|
|
```
|
|
|
|
|
|
|
|
There are a couple of important settings that can be provided to this:
|
|
|
|
```ruby
|
|
|
|
# This means that nil value will be treated as false, the default is false, hence treated as true
|
|
|
|
period_for :period, pessimistic: true
|
|
|
|
|
|
|
|
# You can define a column or a value to be used as a threshold
|
|
|
|
period_for :period, threshold: :interval
|
|
|
|
period_for :period, threshold: 15.minutes
|
|
|
|
|
|
|
|
# This will force the creation of all the methods, which would raise an exception on conflicting. The default is false
|
|
|
|
period_for :period, force: true
|
|
|
|
|
|
|
|
# You can also rename any method that will be created
|
|
|
|
period_for :period, methods: { current: :ongoing, current?: :ongoing? }
|
|
|
|
```
|
|
|
|
|
|
|
|
You can check the list of the methods that will be created on the configuration page for [`period.method_names`](https://github.com/crashtech/torque-postgresql/wiki/Configuring#enum.namespace).
|
|
|
|
|
|
|
|
### Scopes
|
|
|
|
|
|
|
|
This is where the period has its best features. With now provided [Arel](https://github.com/crashtech/torque-postgresql/wiki/Arel) operators, a bunch of well-prepared statements can be used to query the records.
|
|
|
|
|
|
|
|
The `default` represents what was defined by the opposite of `pessimistic` option. If `pessimistic` is `TRUE`, then querying against `NULL` values will result in `FALSE`.
|
|
|
|
|
|
|
|
#### `:current_on` as `.period_on(time)`
|
|
|
|
It basically checks if the range contains the given value or arel attribute.
|
|
|
|
```
|
|
|
|
COALESCE(NULLIF("events"."period", tsrange(NULL,NULL)) @> value, default)
|
|
|
|
-- With threshold
|
|
|
|
COALESCE(NULLIF(tsrange(LOWER("events"."period") - "events"."interval",UPPER("events"."period") + "events"."interval"), tsrange(NULL,NULL)) @> value, default)
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:current` as `.current_period`
|
|
|
|
Checks if the period contains the current time/date.
|
|
|
|
```
|
|
|
|
COALESCE(NULLIF("events"."period", tsrange(NULL,NULL)) @> Time.zone.now, default)
|
|
|
|
- With threshold -
|
|
|
|
COALESCE(NULLIF(tsrange(LOWER("events"."period") - "events"."interval",UPPER("events"."period") + "events"."interval"), tsrange(NULL,NULL)) @> Time.zone.now, default)
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:not_current` as `.not_current_period`
|
|
|
|
The opposite version of the `:current` scope.
|
|
|
|
```
|
|
|
|
NOT COALESCE(NULLIF("events"."period", tsrange(NULL,NULL)) @> Time.zone.now, default)
|
|
|
|
- With threshold -
|
|
|
|
NOT COALESCE(NULLIF(tsrange(LOWER("events"."period") - "events"."interval",UPPER("events"."period") + "events"."interval"), tsrange(NULL,NULL)) @> Time.zone.now, default)
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:containing` as `.period_containing(value)`
|
|
|
|
Checks if the value contains in the range. You can pass either an Arel attribute or a plain value.
|
|
|
|
```
|
|
|
|
"events"."period" @> value
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:not_containing` as `.period_not_containing(value)`
|
|
|
|
The opposite version of the `:containing` scope.
|
|
|
|
```
|
|
|
|
NOT "events"."period" @> value1
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:overlapping` as `.period_overlapping(left, right = nil)`
|
|
|
|
Checks if two ranges overlap. You can pass either another range column as an Arel attribute, a plain range on the left, or the 2 parts of a range.
|
|
|
|
```
|
|
|
|
"events"."period" && value
|
|
|
|
- OR -
|
|
|
|
"events"."period" && tsrange(left, right)
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:not_overlapping` as `.period_not_overlapping(left, right = nil)`
|
|
|
|
The opposite version of the `:overlapping` scope.
|
|
|
|
```
|
|
|
|
NOT "events"."period" && value
|
|
|
|
- OR -
|
|
|
|
NOT "events"."period" && tsrange(left, right)
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:starting_after` as `.period_starting_after(value)`
|
|
|
|
Filter records that the left value is greater than the one provided, which can be either an Arel attribute or a plain value.
|
|
|
|
```
|
|
|
|
LOWER("events"."period") > value
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:starting_before` as `.period_starting_before(value)`
|
|
|
|
Filter records that the left value is less than the one provided, which can be either an Arel attribute or a plain value.
|
|
|
|
```
|
|
|
|
LOWER("events"."period") < value
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:finishing_after` as `.period_finishing_after(value)`
|
|
|
|
Filter records that the right value is greater than the one provided, which can be either an Arel attribute or a plain value.
|
|
|
|
```
|
|
|
|
UPPER("events"."period") > value
|
|
|
|
```
|
|
|
|
|
|
|
|
#### `:finishing_before` as `.period_finishing_before(value)`
|
|
|
|
Filter records that the right value is less than the one provided, which can be either an Arel attribute or a plain value.
|
|
|
|
```
|
|
|
|
UPPER("events"."period") < value
|
|
|
|
``` |
|
|
|
\ No newline at end of file |