How to count records per day?

Starefossen

I have this db table named log and in this i have two fields id and time. Id is primary key (AUTO_INCREMENT) and time is stored as input time, time();

So my question is, how can I count records per day?

I'm trying with this but I can't seam to get it to work:

SELECT count(id) AS count, (date(time)) AS thedate
FROM log
GROUP BY thedate
ORDER BY thedate ASC

And is shows up like this:

Date-Count
empty_date-35
2012-03-07-1

The table has a totale of 35 records and they are submitted over the past 2 moth so all of the shouldn't be grouped together!

Anyone who think they know what the problem is?

Roger_Ramjet

Your problem appears to be that you are storing a timestamp, ie date and time, but merely the time via the time() function. Since they all therefore lack the date component, your query returns empty_date/null and they all get grouped together.

Change the column to a timestamp and you'll get the data you need. Pay attention to the differences between mysql versions.

Starefossen

Dosn't time(); return a timestamp?

nemonoman

No.

bradgrafelman

[man]time/man returns a Unix timestamp (i.e. an integer); MySQL's definition of a TIMESTAMP is quite different.

nemonoman

if you create a TIMESTAMP column, MySQL will automatically populate it with the current DATE and current TIME (NOTE: don't enter a value for the timestamp column, just leave it out of the INSERT)

TIMESTAMP is a DATETIME datatype: '2008-03-04 01:02:03'

DATE is just the date part of DATETIME ( '2008-03-04')
TIME is just the TIME part of DATETIME( '01:02:03')

Unless explicitly set in the SQL, TIMESTAMP columns reflect the last UPDATE DATETIME of the record.

Starefossen

Oki, thanks for explaining 🙂