Database: complex where clause with time

ydenda

Hello together,

I have stupid issue with the database request:

this works:

$deleted_rows = $this->database->query("DELETE FROM users WHERE status='pending' AND reg_date < (NOW() - INTERVAL 24 HOUR)");

this not:

$deleted_rows = $this->database->table('users')->where('status = ?','pending')->where('reg_date < ?',"reg_date < (NOW() - INTERVAL 24 HOUR)")->delete();

on the second example TRACY reports sql error SQLSTATE[22007]: Invalid datetime format

So I presume Nette provides any formatting of mysql time/date. How can I get rid off it or what is the best practice?

PS:I try to perform the time range check using mysql, which should be faster than php way…

CZechBoY

What about

$deleted_rows = $this->database->table('users')->where('status = ?','pending')->where("reg_date < (NOW() - INTERVAL 24 HOUR)")->delete();

ydenda

Hi CZechBoY,

it works; shame I haven't figured it out, thanks.

Any idea why it didn't work in the recommended way (the 2nd php code in my quesetion)?

CZechBoY

Hi @ydenda,
try SqlLiteral, I guess that your argument was escaped and then it didn't work as you expected?

$deleted_rows = $this->database->table('users')
	->where('status = ?','pending')
	->where('reg_date < ?', new SqlLiteral("(NOW() - INTERVAL 24 HOUR)"))
	->delete();

and probably 2nd mistake was that your parameter contains condition too reg_date < (NOW() - INTERVAL 24 HOUR).

ydenda

SqlLiteral did the thing. Thank you.