IF and AND in MySQL query

Question

I have two tables:

Concert:
id | name | date
1  | aaa  | 2016-02-14
2  | bbb  | 2016-02-15

Times:
id | concert_id | time
1  | 1          | 10:00:00
2  | 1          | 16:00:00
3  | 1          | 21:00:00
4  | 2          | 10:00:00
5  | 2          | 15:00:00
6  | 2          | 21:00:00

I would like get concerts by day. For example for "2016-02-14":

SELECT * FROM Concert as c LEFT JOIN Times as t ON c.id = t.concert_id WHERE c.date = '2016-02-14' AND t.time > CURTIME()

This working ok, return only Times with ID 3, but for:

SELECT * FROM Concert as c LEFT JOIN Times as t ON c.id = t.concert_id WHERE c.date = '2016-02-15' AND t.time > CURTIME()

also returns last ID (6). Should be all times (4, 5, 6), because this is next day.

How can I compare CURTIME with CURDATE for my example? IF expression? But how?

You will get different results depending on when you execute each query since the CURTIME() function returns the current time of the server. — Nick, Feb 14 '16 at 18:59
What would you store times in one table and dates in another? — Gordon Linoff, Feb 14 '16 at 19:30

score 0 · Answer 1 · answered Feb 14 '16 at 19:03

You can use CURDATE for the actual date

  SELECT * 
  FROM Concert as c 
  LEFT JOIN Times as t ON c.id = t.concert_id 
  WHERE c.date = CURDATE() AND t.time > CURTIME();

or convert the date you prefer

  SELECT * 
  FROM Concert as c 
  LEFT JOIN Times as t ON c.id = t.concert_id 
  WHERE c.date =str_to_date('2016-02-14', '%Y/%m/%d') 
  AND t.time > CURTIME();
  str_to_date('2016-02-14', '%Y/%m/%d')

score 0 · Answer 2 · answered Feb 14 '16 at 19:07

This could also be a timezone issue. Please refer to the link below to check your timezone on mysql:

http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql

Also, your query:

SELECT * FROM Concert as c LEFT JOIN Times as t ON c.id = t.concert_id WHERE c.date = '2016-02-15' AND t.time > CURTIME()

If you're calling this from PHP you can replace CURTIME() with whatever current date is given in PHP (and you can echo the query to debug).

As it is, really depends on what time is CURTIME(), because if your timezone is different in MySQL, you may think you are running a query at 20:00, when actually it could be 23:00...

score 0 · Accepted Answer · answered Feb 14 '16 at 19:29

It sounds like you just want NOW(). But that doesn't work because the time and dates are in different columns. The basic query for upcoming concerts is:

SELECT *
FROM Concert c JOIN
     Times t
     ON c.id = t.concert_id
WHERE (c.date = '2016-02-14' AND t.time > CURTIME() ) OR
      (c.date > '2016-02-14');

Actually, you can replace '2016-02-14' with CURDATE().

IF and AND in MySQL query

3 Answers3