Postgres 9.2 select multiple specific rows in one query

Question

I have a table with three columns,

• Id Serial.
• Value real.
• timein Timestamp.

I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two rows per day, if it does contain two rows, I want to select the second row plus all the rows following it in the period of time.

To clarify:

id | value | timein
1  | 20.5  | 2013-04-25 11:25:42
2  |  4.2  | 2013-04-26 09:10:42
3  |  3.1  | 2013-04-26 15:45:42
4  |  100  | 2013-04-27 14:52:42
5  | 15.5  | 2013-04-28 17:41:42
6  | 23.3  | 2013-04-29 07:32:42
7  | 21.4  | 2013-04-29 14:32:42

If I wanted to select the values from day 26 (only the second row) plus all the values until day 29 and so on, can it be done in one query?

Answer 1

UNION ALL

I would go with a simple UNION ALL query here:

(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-27 0:0'
ORDER  BY timein DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-27 0:0'
AND    timein <  '2013-04-30 0:0'
ORDER  BY timein
);

This is a single query to Postgres.
Parentheses are required in this case.

NOT EXISTS

Alternative, most likely slower:

SELECT *
FROM   tbl t
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-30 0:0'
AND    NOT EXISTS (
   SELECT 1 FROM tbl t1
   WHERE  t1.timein >= '2013-04-26 0:0'
   AND    t1.timein <  '2013-04-27 0:0'
   AND    t1.timein > t.timein
    )
ORDER  BY timein;

-> SQLfiddle.

Answer 2

SQL Fiddle

select *
from tbl
where
    timein >= (
        select coalesce(max(timein), '2013-04-26')
        from tbl
        where timein::date = '2013-04-26'
    )
    and timein < '2013-04-30'
order by timein