1

How to include in an SQL OVERLAPS the start and the end date?

Like <= >=. How to?

Because it seeems to me that OVERLAPS check only the range in between, but for example:

Date 01 Jan 2001 - 05 Jan 2001 are already in database,

I choose the 5 Jan as the first date of the new range and it says to me that is good. Instead I want OVERLAPS = true because 5 Jan 2001 is already in the database and I cannot start the new range from 5 Jan but from 6 Jan.

How to include extreme dates in OVERLAPS?

  • From the [documentation](http://www.postgresql.org/docs/9.0/static/functions-datetime.html) : `Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.` – Marth Jul 26 '15 at 14:58
  • @Marth, so a solution? Can you help me? –  Jul 26 '15 at 15:04
  • I would probably use @gordonlinoff 's solution. Another way (also excluding the "add 1 day" ~hack) would be to construct closed `daterange`s and then use the `&&` operator. However I do not have any benchmark for these solutions, but if you do do one, please share! – Marth Jul 26 '15 at 15:11
  • @Marth, I don't understand your `daterange's` solution. Can you explain it better? –  Jul 26 '15 at 15:13
  • just posted it as answer. Please ask if anything is still unclear. – Marth Jul 26 '15 at 15:15
  • This question needs a table definition and the exact query you tried (even if it's not working). The best solution may depend on your data types. And *always* your Postgres version. Closely related answer with detailed explanation: http://stackoverflow.com/a/15305292/939860 – Erwin Brandstetter Jul 26 '15 at 16:16

3 Answers3

3

You could use closed dateranges (the '[]' argument) and the && (overlap) operator :

SELECT daterange('2001-01-01', '2001-01-05', '[]') &&
       daterange('2001-01-05', '2001-01-10', '[]');
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘

Note that using ranges also give you access to a lot of useful operators.

Marth
  • 23,920
  • 3
  • 60
  • 72
  • `&&` is even more proprietary than `OVERLAPS` and uses the same logic :-) – dnoeth Jul 26 '15 at 15:28
  • @dnoeth : Well, yeah, but you can closed instead of half-open. Though to be honest I would probably go with Gordon Linoff's solution anyway. – Marth Jul 26 '15 at 15:29
  • Of course you're right, I overlooked the `[]`. That's a nice extension, but the operator syntax is really cryptic.... – dnoeth Jul 26 '15 at 15:40
1

Interesting. I don't use overlaps for two reasons. The first is that it is only available in Postgres (and I don't like to get too used to constructs only available in one database). The second is that simple equalities are sufficient.

Two intervals overlap when the first starts on or before the second ends and the first ends on or after the second starts. Here is a schematic of the logic you want:

where first.start <= second.end and first.end >= second.start
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But maybe OVERLAPS is more efficient if i'm using just and only PostfreSQL? –  Jul 26 '15 at 14:57
  • @JohnSam . . . That is possible. If so, you can add/subtract one day from your ranges. – Gordon Linoff Jul 26 '15 at 14:58
  • I think the solution of subtract date is like an hack and I wouldn't use it. I need a good solution. It'll be interesting to benchmark the efficiently from `OVERLAPS` and `<=` solution. How can I test it with a lot of seeds? –  Jul 26 '15 at 15:03
  • 1
    Worth noting that `OVERLAPS` is *standard* SQL. – Erwin Brandstetter Jul 26 '15 at 16:17
  • 1
    @ErwinBrandstetter . . . That is good to note, and when other databases start supporting it, I will be more inclined to use it. – Gordon Linoff Jul 27 '15 at 02:44
1

Standard SQL's OVERLAP is based on a half-open period, i.e. the begin is included while the end is excluded.

This is the from the ISO 9075 document:

The result of the <overlaps predicate> is the result of the following expression:
( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )

It's a bit strange for a human being, but only only as long as you deal with DATEs: Monday to Wednesday overlaps Wednesday to Friday, because one assumes it's the end of Wednesday for the end date but the begin for the start date.

But this changes when you switch to TIMESTAMPs: Monday morning to Wednesday noon doesn't overlap Wednesday noon to Friday evening.

In fact it's much easier to work with half-open intervals because the begin of the next period is exactly the end of the previous period and you don't have to think about adding a day/second/milli-second.

I'm used to that for years for implementing temporal tables (slowly changing dimensions), which are Standard SQL now and implemented in Teradata, DB2 and the next release of SQL Server.

So you should consider changing your implementation :-) If you can't you can either add/subtract a day before using OVERLAPS or use Gordon's logic, do what's easier for you and don't care that much about efficiency. (I prefer the 1st because Teradata supports some nice functionality for periods which is hard to rewrite).

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Or maybe I can subtract minutes from a date (or add, it's the same). Thanks. –  Jul 26 '15 at 15:52