Tell me more ×
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 select a date within the time period of one day. A simple query like this will do the trick:

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

My problem is I am using prepared statements with pg_execute/PDO(the problem is the same in both). For example:

$query = "SELECT * 
FROM table_name
WHERE table_name.the_date > $1";

    $params = array('DATE_SUB(CURDATE(), INTERVAL 1 DAY)')

    $results = pg_prepare($dbconn, '' , $query );

    $results = pg_execute($dbconn, '', $params);

The result is that it always errors out with: 

pg_execute(): Query failed: ERROR: invalid input syntax for type timestamp:

Now I now there is special postgres functions that can be used as indicated in this link: http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#AEN5582 . Now I've replaced NOW() with the special input 'now' , but there is still an error with the 'INTERVAL 1 DAY' .

How can I get around this?

share|improve this question

1 Answer

up vote 0 down vote

I don't get why you'd want to use bind variables here.

You can simply do: 

$query = "SELECT * 
          FROM table_name
          WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)";

$params = array();
$results = pg_prepare($dbconn, '' , $query);
$results = pg_execute($dbconn, '', $params);

If you want the ability to change the interval, you must still hard-code any syntax elements, although you should be able to dynamically configure the size of the interval.

$query = "SELECT * 
          FROM table_name
          WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL $1 DAY)";

$params = array('1');
$results = pg_prepare($dbconn, '' , $query);
$results = pg_execute($dbconn, '', $params);
share|improve this answer

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.