current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to run a query using 2 timestamps. I'm not sure how to add a day to the "to" timestamp. I would like to add a day, therefore 23:59 on 03-31 would be included. If there is a better way to do this inclusive search, feel free to let me know as well.

$from = "2014-01-01";
$to = "2014-03-31";

pg_prepare($db, 'my_query'
        , "select * from table where from >= $1 and to < $2 + interval '1' day");

$results = pg_execute($db,'my_query',array($from, $to));
share|improve this question
1  
where foo between '2014-01-01' and '2014-03-31' is about all you're need. between is inclusive, and can be re-written as where foo >= '2014-01-01' AND foo <= '2014-03-31' –  Marc B Jun 30 at 21:53
    
Will this include times between '2014-03-31 00:00:01' and '2014-03-31 23:59:59' –  KingKongFrog Jun 30 at 21:55
    
where foo:date, if it's a datetime field? if all you're interested in is the date range, then ignore/suppress the time values. –  Marc B Jun 30 at 21:58
    
'2014-03-31 23:59:59' isn't the last instant of 2014-03-31. PostgreSQL defaults to microsecond precision in timestamps. –  Mike Sherrill 'Cat Recall' Jun 30 at 22:56
add comment

2 Answers 2

up vote 0 down vote 
pg_prepare($db, 'my_query', "
    select * 
    from table 
    where \"from\" >= $1 and to < $2::date + interval '1' day
");

Since from is a reserved word it must be double-quoted. Yes you need to add a day so the whole day of 03-31 is included.

share|improve this answer
add comment
up vote 0 down vote

Just add an integer to a date (which would not work on a timestamp):

pg_prepare($db, 'my_query', "
    select * 
    from table 
    where \"from\" >= $1 and \"to\" < $2::date + 1");

Plus, what @Clodoaldo wrote about reserved words, and to is reserved as well.
It would be wise not to use reserved words as identifiers at all.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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