PostgreSQL – Get Seconds of Day

Get qualifying rows only

One way ..

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
, y AS (
   SELECT *
   FROM   x
   WHERE  x.rn = 1
   AND    date_out >= '2012-10-01'
   AND    date_out <  '2012-11-01'
   )
, z AS (
   SELECT x.*
   FROM   x
   JOIN   y USING (reference)
   WHERE  x.rn = 2
   AND    x.centreid = 1
   )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y
JOIN   z USING (reference)

UNION  ALL
SELECT taskid, reference, centreid, date_out
FROM   z
ORDER  BY reference, date_out;

Another way:

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT x.*, y.taskid AS taskid2, y.centreid AS centreid2, y.date_out AS date_out2
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y

UNION  ALL
SELECT y.taskid2, y.reference, y.centreid2, y.date_out2
FROM   y
ORDER  BY reference, date_out;

I'd expect the second one to be faster. Depends on your data distribution. Test with EXPLAIN ANALYZE.

Get all rows for qualifying references

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT reference
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT *
FROM   tbl
JOIN   y USING (reference)
ORDER  BY reference, date_out;

-> sqlfiddle

Answer to follow-up in comment

Separate groups if more than 30 days between entries.

WITH a AS (
   SELECT *
         ,lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) AS last_date
         ,CASE WHEN date_out > 
                   (lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) - 30)
               THEN 0 ELSE 1
          END AS step
   FROM   tbl
   )
,b AS (
   SELECT *
         ,sum(step) OVER (PARTITION BY reference ORDER BY date_out DESC) AS grp
   FROM a
   )
 ,c AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference, grp ORDER BY date_out DESC) AS rn
   FROM b
   )
,d AS (
   SELECT reference, grp
   FROM   c
   JOIN   c d USING (reference, grp)
   WHERE  c.rn = 1
   AND    c.date_out >= '2012-10-01'
   AND    c.date_out <  '2012-11-01'
   AND    d.rn = 2
   AND    d.centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
ORDER  BY reference, date_out

-> sqlfiddle

But while pure SQL is a beaut .. I would solve this procedurally in a plpgsql function. Very similar to this recent answer on SO. Would be faster, because it can be done in a single table scan.

This actually turned out to be fairly painful. :) The details of this logic ran though my head most of last night... but in my current sleep-deprived state, I think I have devised a solution where every case is handled, and which does what we need in a reasonably-optimal fashion.

It returns the correct values for all of the test cases.

You need an index on (EventTime) so that the optimizer can turn the date-level tests into constants and use that index to find the matching rows by date range... then the server can filter the date-matched rows based on their time match in the CASE statement of the where clause.

In the WHERE clause, the CASE expression matches a condition in our data, and will yield a boolean value based on the truthiness of the expressions in the THEN. This value will include a row if TRUE and exclude the row if FALSE.

I've used variables, but that's mostly for clarity... assuming there's any clarity to be found in what I've written, below, and to prevent errors since the literals are needed in so many different places.

SET @TargetDateTime = '2013-09-26 00:00:00';
SET @DateRange = 7;
SET @TimeRange = 15;

SELECT E.*
 FROM EventTable E
      -- must be at or after midnight the morning of the first valid day
WHERE EventTime >= DATE_SUB(DATE(@TargetDateTime), INTERVAL @DateRange DAY)
      -- must be before midnight on the morning after the first valid day 
  AND EventTime <  DATE_ADD(DATE(@TargetDateTime), INTERVAL (@DateRange + 1) DAY)
      -- now, hold my beer and watch this...
  AND CASE 
  -- easy first case, the time portions of both values are equal
  WHEN TIME(EventTime) = TIME(@TargetDateTime) 
  -- in each case if the expression in the "THEN" evaluates to something true, 
  -- then the row will be returned if the other conditions in WHERE are also met
  THEN TRUE
  -- next case, if the event appears to be earlier than the target, 
  -- we match the row IF either...
  WHEN TIME(EventTime) < TIME(@TargetDateTime)
  -- ...the simple case, difference in seconds is within range
  THEN TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR 
  -- ...the complicated case, when we span midnight 
       86400 + TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime) <= (@TimeRange * 60)
  -- next case, event appears later than target, we match if either...
  WHEN TIME(EventTime) > TIME(@TargetDateTime)
  -- ... simple subtraction returns a value in range
  THEN TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR
  -- ... we wrap around midnight
      86400 + TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime) <= (@TimeRange * 60)
  -- ELSE isn't encountered unless we run into null values, in which case, no match
  ELSE FALSE END; /* END of CASE expression */

A working demo of the final version is here: http://sqlfiddle.com/#!2/7b8e5/2

It is usually bad form in a WHERE clause to use columns as arguments to functions, because this prevents the optimizer from using an available index, since it has to evaluate the data in every row of the table in order to evaluate the function. There's no harm, here, though, since an index can't help us match the times. This is because DATETIME values are on the left hand side of the field, as stored, and the time values are on the right, and an index stops helping as soon as we encounter irrelevant information from left to right. (That's usually discussed in the context of multi-column indexes, but it also holds true, here.)

It's also often bad form in a WHERE clause to use a CASE expression because that too may prevent the optimizer from choosing the optimal path because the CASE can prevent the optimizer from forming a proper understanding of the logic, but this is acceptable here because we have nothing but AND in the WHERE clause and two other conditions that are easily satisfied by an index.

Update: This code and the answer went through several iterations before arriving at its current (working) form, as you can see in the comments.

Essentially, what we are doing is considering the three cases than can be identified when trying to compare wall-clock time of day: the times are equal, A is earlier than B, or B is earlier than A. It seems like "absolute value" might come into play, but the problem we encounter is that when a time value has no date component, earlier and later aren't meaningful concepts when the times might span midnight. Without a date component, 00:05:00 and 23:55:00 are 23 hours and 50 minutes apart. With a date component, they could also be "n days and 10 minutes" apart. When the date component is missing but uninteresting (or already handled, in this case), we first check the simple case, subtracting the "earlier" from the "later" to see if they're within range, but when they're not, we add 86400 seconds to the "earlier" component, essentially making it "the same time, the following day"... then the subtraction can be done to see if the times are close enough together when they're calculated as having spanned midnight.