Heri Heri - 15 days ago 5
MySQL Question

I want to change query the date between current date but with different times?

i want to change 2016-11-20 with current date

SELECT services, loket,
SUM( CASE WHEN STATUS IN ('7','4') THEN 1 ELSE 0 END ) AS Total_queue,
SUM( CASE WHEN STATUS = '4' THEN 1 ELSE 0 END ) AS balance_queue, SUM( CASE WHEN STATUS = '7' THEN 1 ELSE 0 END ) AS nomer_queue
FROM tabelqueue
WHERE STATUS IN ('7','4')
AND get_ticket BETWEEN '2016-11-21 00:00:01' AND '2016-11-21 23:59:59'
GROUP BY services

Answer

Write the condition as:

WHERE STATUS IN ('7', '4') AND
      get_ticket >= CURDATE() AND get_ticket < DATE_ADD(CURDATE(), interval 1 day)

Your original condition should probably be written as:

WHERE STATUS IN ('7', '4') AND
      get_ticket >= DATE('2016-11-21') AND
      get_ticket < DATE_ADD(DATE('2016-11-21'), interval 1 day)

Notes:

  • Don't use times when you only want dates.
  • You could do DATE(get_ticket) = CURDATE(). This is easy to read but the query cannot take advantage of an index on get_ticket, if that is available and appropriate.
  • If STATUS is a number, do not use quotes around the constants. Quoted numbers are misleading both for people and for the optimizer.
Comments