count by current timestamp

[Tay Thotheolh]

Hi.

I have a database containing a column called 'datetime' and it is type of TIMESTAMP.

The database is used for forum posts in my own web application and each posts have to have it's timestamp recorded for reference and tracking.

How do I use COUNT on the datetime for the current date. For example, I have some datetime in the database and I want to count how many of the datetime belongs to today's entry.

I want to collect information on the amount of posts per day.

I am using MySQL for the database by the way.

[Kalyan Anand]

Did you try TO_DATE(ColumnDate , 'DD-MON-YYYY') = Today's Date ?

[Tay Thotheolh]

I am not sure on how you use the TO_DATE.

Currently I am doing 'SELECT COUNT(datetime) AS Hits, enteredtime FROM forumpost GROUP BY DATE(datetime)'

It shows:

+----+----------------------+
|Hits| DateTime |
+----+----------------------+
|8���| 2008-07-30 17:53:58.0|
|42��|�2008-07-31 13:13:53.0|
|21��|�2008-08-01 16:20:19.0|
|7���| 2008-08-04 16:15:25.0|
|32��|�2008-08-05 16:35:41.0|
|1���| 2008-08-07 10:59:49.0|
|41��|�2008-08-11 17:15:13.0|
|45��|�2008-08-12 16:13:10.0|
|47��|�2008-08-13 12:33:51.0|
|27��|�2008-08-18 16:59:19.0|
|119�|�2008-08-19 10:23:39.0|
|1���| 2008-09-05 16:48:29.0|
+----+----------------------+

When using some database access tool the above was shown using the statement.

[Paul Sturrock]

BETWEEN might be the keyword you need.

[Tay Thotheolh]

So how do I modify my sql statement to include the between ?

[Paul Sturrock]

See this.

Your logic is just to alter your current SQL statement to include a where or having clause that is between the start of the chosen day to the end of the chosen day. You don;t need to return the enteredtime, just the count. Santhosh Jali has given you a hint how to enter dates into your sql query - I don't think MySQL has a TO_DATE funtion however. Check the docs but I think their equivalent is str_to_date. The lower date can be the start of the day, the upper date can be now (again check the docs, but I think in MySQL you get that witgh the NOW() function).
[ September 30, 2008: Message edited by: Paul Sturrock ]

[Paul Clapham]

If the DATE() function in MySQL really returns the date component of a timestamp, then I suggest you need something like this:

SELECT DATE(datetime) as TheDate, COUNT(*) AS Hits FROM forumpost GROUP BY TheDate