• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

count by current timestamp

 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you try TO_DATE(ColumnDate , 'DD-MON-YYYY') = Today's Date ?
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
BETWEEN might be the keyword you need.
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So how do I modify my sql statement to include the between ?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 20986
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic