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