Question 113304 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query on date part of Timestamp

I’m trying to return 2 columns for a graph on with date and the other with count of jobs for the date.
Jobdate is a timestamp however. So although the first query is instant, it will give a new row for every time change as well.

 SELECT JOBDATE ,COUNT(JOBNO) AS TOTAL FROM JOB 
 where JOBDATE > '2022-09-20'
 GROUP BY JOBDATE
  ORDER BY JOBDATE

The query below works fine except that it takes 30 seconds to run over a small dataset.

 SELECT CAST(JOBDATE AS DATE)  AS THEDATE,COUNT(JOBNO) AS TOTAL FROM JOB 
 where CAST(JOBDATE AS DATE) > '2022-09-20'
 GROUP BY THEDATE
  ORDER BY THEDATE

On option is to have a second JOBDATE date only field. Has anyone got a better idea?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Sorted. For anyone one else who needs to know -

    SELECT date(JOBDATE) ,COUNT(JOBNO) AS TOTAL 
FROM JOB 
where JOBDATE > '2022-09-20'
GROUP BY date(JOBDATE)

  2. 0

    I can only assume the casts are causing the slowdown. They are replaced with date() and is much better

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search