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:

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.

When you GROUP BY you must use the same syntax as in the SELECT i.e. DATE_FORMAT( rstf_created, '%e %d %Y' )

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:

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

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

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:

Cheers for your time.


Tony