aspose file tools*
The moose likes JDBC and the fly likes count by current timestamp Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "count by current timestamp" Watch "count by current timestamp" New topic
Author

count by current timestamp

Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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

Joined: Feb 07, 2007
Posts: 194
Did you try TO_DATE(ColumnDate , 'DD-MON-YYYY') = Today's Date ?
Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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

Joined: Apr 14, 2004
Posts: 10336

BETWEEN might be the keyword you need.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
So how do I modify my sql statement to include the between ?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Bartender

Joined: Oct 14, 2005
Posts: 18127
    
    8

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.
 
subject: count by current timestamp
 
Similar Threads
Subtract Minutes from a DateTime object
Timestamp in GMT and the difference
Insert a timestamp into mysql database
MySQL JDBC Driver and Null Dates
how to change the date,Time format after accessing it from the database