Checking overlaps between times

Question

I try to create validation that checks times overlapping.

  scope :overlapping_activity, ->(activity) { where("(start_on, end_on) OVERLAPS (?, ?)",
                                              activity.start_on, activity.end_on }
  validate :not_overlapping_activity

  def not_overlapping_activity
    if Activity.overlapping_activity(self).any?
      errors.add(:base, "Zajęcie w tym okresie koliduje z innymi.")
    end
  end

Error:

ERROR:  function pg_catalog.overlaps(time without time zone, time without time zone, unknown, unknown) is not unique
LINE 1: ...NT(*) FROM "activities" WHERE ((start_on, end_on) OVERLAPS (...

I found this topic: Rails 3.1: Querying Postgres for records within a time range, but I cannot use it in my solution. While I try add timestamps in scope, it shows another syntax errors. Could someone show me how to properly resolve my issue?

How I tried use timestamp:

  scope :overlapping_activity, ->(activity) { where("(start_on::timestamp, end_on::timestamp) OVERLAPS (?, ?)",
                                              timestamp activity.start_on, timestamp activity.end_on) }

Could you try ` scope :overlapping_activity, ->(activity) { where("(start_on::timestamp, end_on::timestamp) OVERLAPS (timestamp :start_on, timestamp :end_on)", :start_on => activity.start_on, :end_on => activity.end_on) }` ? — IVO GELOV, Aug 10 '16 at 11:59
@IVOGELOV now it's error: `ERROR: cannot cast type time without time zone to timestamp without time zone` — Prezes Łukasz, Aug 10 '16 at 12:02
Are you sure that `activity.start_on` and `activity.end_on` include a date and not only the time ? — IVO GELOV, Aug 10 '16 at 12:24
@IVOGELOV as I write in title of post there are only times. In PostgreSQL database columns are time without time zone — Prezes Łukasz, Aug 10 '16 at 12:30
The `OVERLAPS` requires that all 4 arguments are of type TIMESTAMP - date PLUS time. If you can not convert your times to timestamp (adding any date, but the same date to all 4 arguments) - then your only option is to convert all 4 times into seconds (using `extract (epoch from t::interval)` and use this formula to check for overlapping `GREATEST(0,end_1 - start_1 - GREATEST(0,end_1 - end_2) - GREATEST(0,start_2 - start_1))` — IVO GELOV, Aug 10 '16 at 13:50
@IVOGELOV could you write solution, because I don't have idea how to use your tip... — Prezes Łukasz, Aug 10 '16 at 14:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120611/discussion-between-lukasz-korol-and-ivo-gelov). — Prezes Łukasz, Aug 10 '16 at 14:41

score 1 · Accepted Answer · answered Aug 10 '16 at 16:13

I achieved what I wanted by this method:

validate :not_overlapping_activity

    def not_overlapping_activity
      overlapping_activity = Activity.where(day_of_week: day_of_week)
                                     .where(pool_zone: pool_zone)
      overlapping_activity.each do |oa|
        if (start_on...end_on).overlaps?(oa.start_on...oa.end_on)
          errors.add(:base, 'In this period of time there is some activity.')
        end
      end
    end

If someone has better solution please write. I want to be better and more clever :)

Checking overlaps between times

1 Answers1