PostgreSQL and serial data

Question

I have a dataset that contains:

Table { date itemName }

The date is for the most part consistent. There are no duplicate dates [since this is the primary key].

The question is divided into several parts (all regarding the use of SQL):

• Can I find spaces in a series of dates listed in a table? For example: 1/2/09-1/3/09No dates
• Is it possible to find sections of dates not in the table that have a range greater than n (this is a number determined at runtime)? For example: For n = 2Dates are 1/2/09-1/3/09not returned, but Dates 5/6/09-6/1/09are.

Answer 1

plsql , . :

date checked_date = 2000-01-01;
int unchecked_section = 0;
while ( checked_date <= today() ) {
  if (! sql(select itemName from Table where itemName=checked_date)) {
    unchecked_section++;
  } else {
    if ( unchecked_section>=n ) {
      print checked_date-unchecked_section, checked_date
    }
    unchecked_section = 0;
  }
  checked_date++;
}
if ( unchecked_section ) {
  print checked_date-unchecked_section, checked_date
}

, . - 365 .

Answer 2

PostgreSQL 8.4, :

SELECT *
    FROM (SELECT itemName, date, date - lag(date) OVER w AS gap
              FROM someTable WINDOW w AS (ORDER BY date)
         ) AS pairs
    WHERE pairs.gap > '1 day'::interval;

Answer 3

After some testing, I came up with the following SQL statement:

SELECT date, itemName
  FROM "Table" as t1
  WHERE NOT EXISTS (
     SELECT date 
     FROM "Table" as t2 
     WHERE t2.date = (t1.date - INTERVAL '1 day')
  )
  ORDER BY date
  OFFSET 1  -- this will skip the first element

This will give you all the lines that do not have a direct successor.

If you change the instruction:

SELECT date, itemName
  FROM "Table" as t1
  WHERE NOT EXISTS (
    SELECT date 
    FROM "Table" as t2 
    WHERE (t2.date >= (t1.date - INTERVAL '2 day'))
    AND (t2.date < t1.date)
  )
  ORDER BY date
  OFFSET 1

you can use the INTERVAL length in a WHERE clause to filter gaps of at least that size.

Hope this helps.