Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: need help with query please

    Hi,

    I'm trying to write a query that returns 2 columns, the date and the number of records that were added to the table on that date. I'm really stuck though because the date is in the form of a timestamp which also includes the time.

    I thought the query would be something like:

    Code:
    SELECT DATE_FORMAT( rstf_created, '%e %d %Y' ) , COUNT( *  )
    FROM `rst_applicants`
    GROUP BY DATE( `rstf_created` )
    This does not work at all however, I get about 6000 records under count for a date of NULL, and about 10 records for dates in the future with 1 count each.

    Can anyone give me a pointer, I'm guessing that my overall logic is off based on the results I'm getting but I also have no idea how to get MySQL to just use the date portion of the timestamp either.

    Any help would be greatly appreciated.


    Many thanks.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When you GROUP BY you must use the same syntax as in the SELECT i.e. DATE_FORMAT( rstf_created, '%e %d %Y' )
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Hi Ronan,

    Thanks for taking the time to reply.

    I tried your suggestion (assuming I understood you correctly) to no avail.

    The query now looks like this:

    Code:
    SELECT DATE_FORMAT( rstf_created, '%e %d %Y' ) , COUNT( *  )
    FROM `rst_applicants`
    GROUP BY DATE_FORMAT(rstf_created, '%e %d %Y' )
    But I get the same results back, one field of date 'NULL' with a count of 6000 records and about 10 records with dates set in the future with a count of 1 each. Also I have just noticed that one of the dates is 31 31 2012 which obviously is impossible.

    Am I using the functions correctly - I assume that you can pass a timestamp to date_format without converting it into a datetime object first?

    Thanks again.

    Tony

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Tony,

    I think the problem is with %e and %d which give the same i.e. the day of the month. I think you need to look at %e %m and %Y.

    Ronan
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    Thanks again Ronan - misread the docs there!

    I spotted the rather handy FROM_UNIXTIME() function as well, in the end the following query did the trick:

    Code:
    SELECT DATE_FORMAT(FROM_UNIXTIME(rstf_created),'%e %m %Y'), COUNT(*) FROM `rst_applicants` GROUP BY DATE_FORMAT(FROM_UNIXTIME(rstf_created),'%e %m %Y')
    Cheers for your time.


    Tony

Posting Permissions

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