Using MySQL's DATE_SUB

When trying to use MySQL's DATE_SUB function, I receive a 'unexpected token' error when it encounters the INTERVAL parameter.

$trendsRow = Metars::find(array(
  "conditions" => "station_id = ?1 AND loaded BETWEEN DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 DAY) AND UTC_TIMESTAMP()",
  "bind"       => array(1 => $this->stationID),
  "order"      => "loaded DESC",
));

Syntax error, unexpected token INTEGER(1), near to ' DAY) AND UTCTIMESTAMP() ORDER BY loaded DESC', when parsing: SELECT [Metars].* FROM [Metars] WHERE stationid = ?1 AND loaded BETWEEN DATESUB(UTCTIMESTAMP(),INTERVAL 1 DAY) AND UTC_TIMESTAMP() ORDER BY loaded DESC (154)



33.9k

Can you try to bind the interval ? I don't know if it will work. It's just a curiosity.

$trendsRow = Metars::find(array(
  "conditions" => "station_id = ?1 AND loaded BETWEEN DATE_SUB(UTC_TIMESTAMP(),INTERVAL ?2 DAY) AND UTC_TIMESTAMP()",
  "bind"       => array(1 => $this->stationID, 2 => 1),
  "order"      => "loaded DESC",
));

Thanks for the suggestion Calin. I still get an error with a slightly different error message:

Syntax error, unexpected token NUMERIC PLACEHOLDER(?2), near to ' DAY) AND UTCTIMESTAMP() ORDER BY loaded DESC', when parsing: SELECT [Metars].* FROM [Metars] WHERE stationid = ?1 AND loaded BETWEEN DATESUB(UTCTIMESTAMP(),INTERVAL ?2 DAY) AND UTC_TIMESTAMP() ORDER BY loaded DESC (155)



33.9k

This works for me:

$test = Test::find(array(
  "station_id = :stationId: AND loaded BETWEEN DATE_SUB(UTC_TIMESTAMP(),INTERVAL :day_interval: DAY) AND UTC_TIMESTAMP()",
  "bind"       => array('stationId' => $this->stationID, 'day_interval' => '1'),
  "order"      => "loaded DESC",
));

For some reason it still throws an error for me.

Syntax error, unexpected token STRING PLACEHOLDER(dayinterval), near to ' DAY) AND UTCTIMESTAMP() ORDER BY loaded DESC', when parsing: SELECT [Metars].* FROM [Metars] WHERE stationid = :stationId: AND loaded BETWEEN DATESUB(UTCTIMESTAMP(),INTERVAL :dayinterval: DAY) AND UTC_TIMESTAMP() ORDER BY loaded DESC (176)

Same error for me: Syntax error, unexpected token STRING PLACEHOLDER(withdraw), near to ' DAY) ORDER BY itemOrder', when parsing: SELECT [Product\Model\ProductSingleValue].* FROM [Product\Model\ProductSingleValue] WHERE productItemId = :pid: AND catalogEntryId = :cid: AND NOW() BETWEEN validFrom AND DATE_ADD(validTill, INTERVAL :withdraw: DAY) ORDER BY itemOrder

PHQL does not support INTERVAL as this is not an standard extension of PHQL, however you can use: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Db#dialectmysqlextended