Select Count all with todays date

[honkmaster]

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.

$d=strtotime("today");
$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 May 26, 2015 by honkmaster

[Barand]

Timestamps have a date and time element (2015-05-26 16:53:30) so will not be equal to today's date (2015-05-26) which has 00:00:00 as its time element.

 

You need to use DATE() function to extract the date portion.

SELECT ... WHERE DATE(history_date) = CURDATE()

[QuickOldCar]

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()";

[Barand]

If it were stored as DATETIME then DATE(`history_date`) would work just the same.

Edited May 26, 2015 by Barand

[Barand]

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

CREATE TABLE `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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   |
+---------------------+------------+---------------------+--------------+---------------------+-------------+

[QuickOldCar]

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.

[Psycho]

@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 

 

@honkmaster:

 

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.

 

 

SELECT COUNT(*) FROM history WHERE DATE(FROM_UNIXTIME(history_date)) = CURDATE()

[Barand]

Way too many date related functions.

 

 

A  luxury after using MS_SQL