• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

count by current timestamp

 
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
BETWEEN might be the keyword you need.
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So how do I modify my sql statement to include the between ?
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic