3

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?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
0
0

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.