Using MySQL's DATE_SUB

Question

Using MySQL's DATE_SUB

Henry Petry Jul '13

Created Jul '13	Last Reply Jun '15	Replies 4	Views 1799	Votes 2

Henry Petry 1.7k

Jul '13

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 UTC_TIMESTAMP() ORDER BY loaded DESC', when parsing: SELECT [Metars].* FROM [Metars] WHERE station_id = ?1 AND loaded BETWEEN DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 DAY) AND UTC_TIMESTAMP() ORDER BY loaded DESC (154)

2

Calin Rada · Answer 1 · 2013-07-22T05:55:22-07:00

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",
));

Henry Petry · Answer 2 · 2013-07-22T06:10:35-07:00

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 UTC_TIMESTAMP() ORDER BY loaded DESC', when parsing: SELECT [Metars].* FROM [Metars] WHERE station_id = ?1 AND loaded BETWEEN DATE_SUB(UTC_TIMESTAMP(),INTERVAL ?2 DAY) AND UTC_TIMESTAMP() ORDER BY loaded DESC (155)

Calin Rada · Answer 3 · 2013-07-22T06:24:19-07:00

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",
));

Henry Petry · Answer 4 · 2013-07-22T06:44:12-07:00

For some reason it still throws an error for me.

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

Ondrej Podolinský · Answer 5 · 2015-06-01T02:00:17-07:00

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

Andres Gutierrez · Answer 6 · 2015-06-02T19:02:47-07:00

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