Jump to content

Select Count all with todays date


Recommended Posts

Hi, I'm stuck on below, I have a table called History that records date and time as timestamp, example attached below


I'm trying to count the number of entries with todays date, below is the php for todays date and the query, Any one help point me in the write direction.

$wholedate = date("Y-m-d", $d);
$date_array = explode('-', $wholedate);
$date = mktime(0,0,0,(int)$date_array[1],(int)$date_array[2],(int)$date_array[0]);

$query_rsWorkedToday = "SELECT COUNT(*) FROM history WHERE history_date = ‘$date’”;


Edited by honkmaster
Link to comment
Share on other sites

Since you saved as a timestamp:

$query_rsWorkedToday = "SELECT COUNT(*) FROM history WHERE DATE(`history_date`) = CURDATE()";


If it was stored as datetime would use:

$query_rsWorkedToday = "SELECT COUNT(*) FROM history DATE_FORMAT(history_date, '%Y-%m-%d') = CURDATE()";

Link to comment
Share on other sites

In fact, as long as the date is in the correct format it works with VARCHAR datetimes too

CREATE TABLE `test_date` (
  `date` datetime DEFAULT NULL,
  `str_date` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)

mysql> SELECT
    ->   date
    -> , DATE(date) as date_only
    -> , timestamp
    -> , DATE(timestamp) as ts_date_only
    -> , str_date
    -> , DATE(str_date) as s_date_only
    -> FROM test_date;
| date                | date_only  | timestamp           | ts_date_only | str_date            | s_date_only |
| 2015-05-26 17:55:00 | 2015-05-26 | 2015-05-26 18:03:31 | 2015-05-26   | 2015-05-26 17:55:00 |2015-05-26   |
Link to comment
Share on other sites

That's interesting Barand, I always used the different functions depending what was saved as.


Way too many date related functions.


I prefer to store as datetime for my own work because is human readable , already string formatted, plus not sure what would happen after 2038 if using timestamp.

Yeah a long way off but I suppose have to think of the future, the last 20 years zipped right past me.

Timestamp has a date range from 1970 to 2038, most likely would make a fix by then, maybe 40 more date related functions.

Link to comment
Share on other sites

@Barand & QuickOldCar:


But, accoridng to his attached image, he is storing the timestamp as a UNIX timestamp - not a MySQL timestamp. So, none of those soltuions will work. He could use 




You shoudl really store dates using one of the native date/time fields supported in your database. Then you can use all the date/time functions thata re natively supported in the database (such as was shown above). In fact, you could just create a DB Timestamp and the field woould be auto-populated whenever you create a new record - without needing to include in in your query. But, you could use FROM_UNIXTIME() with what you have - but I would still say you should change what you are doing to a proper method.



Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.