Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post

    Getting all the rows within a day of a certain date

    I'm writing a calendar, and I want to find a number of rows that within that date. For example, I want to find how many posts occured on the 1st, how many topics occured on the 1st, how many posts occured on the 2nd, so on so forth.

    Currently I'm looping through the month while i'm displaying and running the queries on each day.

    These are just some of the queries that the page is using:

    Code:
    [1] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-02',`dateadded`) = 1
        [2] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-02',`postdate`) = 1
        [3] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-03',`dateadded`) = 1
        [4] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-03',`postdate`) = 1
        [5] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-04',`dateadded`) = 1
        [6] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-04',`postdate`) = 1
        [7] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-05',`dateadded`) = 1
        [8] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-05',`postdate`) = 1
        [9] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-06',`dateadded`) = 1
        [10] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-06',`postdate`) = 1
        [11] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-07',`dateadded`) = 1
        [12] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-07',`postdate`) = 1
        [13] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-08',`dateadded`) = 1
        [14] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-08',`postdate`) = 1
        [15] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-09',`dateadded`) = 1
        [16] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-09',`postdate`) = 1
        [17] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-10',`dateadded`) = 1
        [18] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-10',`postdate`) = 1
        [19] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-11',`dateadded`) = 1
    Ideally what I would be able to do, is have two queries done before I start looping through the rows that I would be able to associate in an array.

    If you have any idea how to help limit the queries necessary, please let me know.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    I'm lost. Why would you do
    Code:
    WHERE DATEDIFF('2009-04-02', dateadded) = 1
    instead of just
    Code:
    WHERE dateadded = '2009-04-03'
    ????

    Is it because the field dateadded has a time component that you want to ignore?

    If so, it's much more efficient to simply do
    Code:
    WHERE DATE(dateadded) = '2009-04-03'
    The MySQL DATE() function strips the time off of a date+time value.

    So...

    The best way to do this is to have a table of all possible dates for the next 10 years or so (one field in the table, one record per every date for 10 years). Build that table once and then use it until the computer gets recycled.

    You can then just do
    Code:
    SELECT allDates.theDate, COUNT(dateadded)
    FROM allDates LEFT JOIN othertable ON allDates.theDate = DATE(dateadded)
    WHERE allDates.theDate BETWEEN '2009-04-02' AND '2009-04-11'
    GROUP BY allDates.theDate
    ORDER BY allDates.theDate
    Works great. You get one record of output for your given WHERE range of dates.

    But...

    But if you aren't willing to do that, then just do:
    Code:
    SELECT DATE(dateadded) AS theDate , count(*) AS howMany
    FROM yourtable
    WHERE DATE(dateadded) BETWEEN '2009-04-02' AND '2009-04-11'
    GROUP BY DATE(dateadded)
    ORDER BY DATE(dateadded)
    Now, you may have some "holes" in there. So as you loop through all the days in your external code (PHP, JSP, whatever), if the date from the set of records is AFTER the loop date, then you know the count for the loop date is zero and you so note it and then DO NOT advance to the next record.

    So maybe your loop is going from 2009-4-2 to 2009-4-10:
    Code:
    loopdate: 2009-4-2
       date from db: 2009-4-2
       get count from db
       advance to next record
    loopdate: 2009-4-3
       date from db: 2009-4-5
       count is zero (no advance)
    loopdate: 2009-4-4
       date from db: 2009-4-5
       count is zero (no advance)
    loopdate: 2009-4-5
       date from db: 2009-4-5
       get count from db
       advance to next record
    In your case, you'd have two recrodsets and you'd just do the same logic on both of them, but you'd only need one loop.

    No?

    In the long run, creating the extra allDates table is worth the little bit of trouble. But up to you.

  • Users who have thanked Old Pedant for this post:

    thesavior (04-09-2009)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    NOTE: for decent performance, of course allDates.theDate should be the primary key. And then *both* dateadded and postdate in your table should be indexed fields.

  • #4
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thanks, this is exactly what I was looking for:

    Code:
    SELECT DATE(dateadded) AS theDate , count(*) AS howMany
    FROM yourtable
    WHERE DATE(dateadded) BETWEEN '2009-04-02' AND '2009-04-11'
    GROUP BY DATE(dateadded)
    ORDER BY DATE(dateadded)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •