SQL: What's the state of ID x on date y (is there a more efficient way?)

Question

I have two tables: one containing a list of IDs and one containing some time related information about those IDs.

example

ID_TABLE
ID
 1
 2
 3

INFO_TABLE
ID         START           END   STATE
 1    2000-01-01    2000-01-31       A
 1    2000-02-01    2000-03-31       B
 3    2000-02-01    2000-02-31       A
 3    2000-03-01    2000-05-31       C

(please notice, that ID=2 is not present in the second table)

Now I want to turn this information into balanced panel data, which means I want to know what STATE is present for every ID on certain dates.

My SQL-Command looks like this:

SELECT
  id_table.id,

  t1.state AS Jan,
  t2.state AS Feb,
  t3.state AS March

  FROM 
    id_table
    LEFT JOIN info_table AS t1
      ON id_table.id = t1.id
      AND '2000-01-15' BETWEEN t1.start AND t1.end
    LEFT JOIN info_table AS t2
      ON id_table.id = t2.id
      AND '2000-02-15' BETWEEN t2.start AND t2.end
    LEFT JOIN info_table AS t3
      ON id_table.id = t3.id
      AND '2000-03-15' BETWEEN t3.start AND t3.end
;

Is there a more efficient way?

Um - presumably the month component of the date should be different each time? You've got it always set to 15th January... — RB., May 31 '11 at 10:53

score 1 · Accepted Answer · edited May 23 '17 at 11:55

I'm not entirely sure I get your question, but if the wikipedia page that you link is anything to go by, what you're really looking for is closer to this:

select t.id,
      '2000-01-01'::date + (m.mon || ' months')::interval as month,
      t.state
from generate_series(0, 11) as m (mon)
left join info_table t
on '2000-01-15'::date + (m.mon || ' months')::interval
   between t.start and t.end

In case your current query is the one you actually need, you can making faster by using a geometry type:

PostgreSQL matching interval between start and end time against timestamp

SQL: What's the state of ID x on date y (is there a more efficient way?)

1 Answers1