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?

2 Answers 2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.