CODEWITHSUNDEEP
mysqlsqldatedatetimewhere-clause
Adrian Preuss
Adrian Preuss

Reputation: 3113

Comapring DATE_ADD with NOW not working on MySQL

I've try to compare an DateTime in MySQL.

The table is to check locked users. For sample, an User will be locked for 1 Day at the DateTime 2020-04-09 14:51:32:

SELECT
    *,
    DATE_ADD(`time_created`, INTERVAL `locked_time` DAY) AS `until`
FROM
    `' . DATABASE_PREFIX . 'users_locked`
WHERE
    `user_id`=:user_id
LIMIT 1

until is the datetime time_created with added days from locked_time.

Here is the Result of entries:

object(stdClass)[53]
  public 'id' => string '1'
  public 'user_id' => string '2'
  public 'locked_by' => string '1'
  public 'time_created' => string '2020-04-09 14:51:32'
  public 'locked_time' => string '1'
  public 'reason' => string 'This is a test'
  public 'until' => string '2020-04-10 14:51:32'

The calculation (time_created with added locked_time with the result 2020-04-10 14:51:32) is correctly. But when i try to compare this value with an current timestamp (like NOW()), no results are available - Whether I use >= or <=:

[...]
WHERE
    `user_id`=:user_id
AND
    `until`<=NOW()
LIMIT 1

[...]
WHERE
    `user_id`=:user_id
AND
    `until`>=NOW()
LIMIT 1

How can I check if the date is 'until' within 'NOW()`?

Upvotes: 0

Views: 33

Answers (1)

GMB
GMB

Reputation: 222482

Expressions defined in the SELECT clause are not available in the WHERE clause (the latter is evaluated before the former). You would need to repeat the expression:

SELECT
    *,
    DATE_ADD(`time_created`, INTERVAL `locked_time` DAY) AS `until`
FROM
    `' . DATABASE_PREFIX . 'users_locked`
WHERE
    `user_id`=:user_id
     AND DATE_ADD(`time_created`, INTERVAL `locked_time` DAY) >= NOW()
LIMIT 1

Upvotes: 1

Related Questions