How to create fast database queries

Inverting date ranges: MySQL

with 3 comments

Answering questions asked on the site.

Maxym asks:

Inverting date ranges is an interesting article.

I had the same problem, and I solved it almost the same way in PostgreSQL. Your solution is great! The problem is to implement it on MySQL :) And of course performance there.

Do you have any idea how to transform this query for MySQL?

The query in question regards inverting contiguous non-overlapping date ranges: transforming them so the the gaps become the ranges and the ranges become the gaps.

The following set:

StartDate EndDate
2009-11-12 2009-11-13
2009-11-15 2009-11-19

should become this:

StartDate EndDate
NULL 2009-11-12
2009-11-13 2009-11-15
2009-11-19 NULL

The link above shows how to make this query in SQL Server and can be applied to Oracle and PostgreSQL 8.4 as well.

However, the query in question relies on FULL OUTER JOIN and ROW_NUMBER. MySQL lacks both of these.

To implement this query in MySQL we need to use a certain workaround.

Let's create a sample table:

Table creation details

Written by Quassnoi

November 12th, 2009 at 11:00 pm

Posted in MySQL

3 Responses to 'Inverting date ranges: MySQL'

Subscribe to comments with RSS

  1. Good morning,
    thx for this part of code, it’s working perfectly for me.
    I’d would like to have the same result, but my table is the same than your t’range table,but with an extra field :id_room .
    Is it possible to get the same result than previous, ordered by id_room ?
    thx a lot.


    2 Jul 13 at 11:27

  2. @julien: please do not post long code in the comments. Instead, prepare your setup on http://sqlfiddle.com and post here the link.


    3 Jul 13 at 13:13

  3. ok, sorry for that.

    In fact, i have understood that this code can do the same than the link above ( in sqlserver).
    But in the link above, there is the idressource in the table, and not in that code in Mysql.

    Is it possible to do the same in Mysql with idressource field ?


    4 Jul 13 at 04:18

Leave a Reply