GROUP BY creation date

I have a table structure which includes a UNIX timestamp (seconds since
'1970-01-01 00:00:00' UTC) for the creation date.

I would like to return a record count grouped by creation date using the
time range from 00:00:00 to 23:59:59 for each respective day.

For example:

Date, Count

24 October, 23
23 October, 12
22 October, 16

Can this one done in one query using only SQL?

Bosconian wrote:
> I have a table structure which includes a UNIX timestamp (seconds since
> '1970-01-01 00:00:00' UTC) for the creation date.
>
> I would like to return a record count grouped by creation date using the
> time range from 00:00:00 to 23:59:59 for each respective day.
>
> For example:
>
> Date, Count
>
> 24 October, 23
> 23 October, 12
> 22 October, 16
>
> Can this one done in one query using only SQL?

Untested, and just a guess:

SELECT DATE( FROM_UNIXTIME( field ) ) AS creation_date,
COUNT(DATE(FROM_UNIXTIME(field)) AS cnt
FROM table
GROUP BY creation_date

"strawberry" <zac.carey@gmail.com> wrote in message
news:1161781148.385250.97210@i3g2000cwc.googlegrou ps.com...
>
> Bosconian wrote:
>> I have a table structure which includes a UNIX timestamp (seconds since
>> '1970-01-01 00:00:00' UTC) for the creation date.
>>
>> I would like to return a record count grouped by creation date using the
>> time range from 00:00:00 to 23:59:59 for each respective day.
>>
>> For example:
>>
>> Date, Count
>>
>> 24 October, 23
>> 23 October, 12
>> 22 October, 16
>>
>> Can this one done in one query using only SQL?
>
> Untested, and just a guess:
>
> SELECT DATE( FROM_UNIXTIME( field ) ) AS creation_date,
> COUNT(DATE(FROM_UNIXTIME(field)) AS cnt
> FROM table
> GROUP BY creation_date
>

Cool, this looks like it works. I just needed to add a third parenthesis
after the COUNT clause. Thanks!