0

I want to show records of today (from yesterday 12 AM to today 11:59 PM), Yesterday, and records of this week, I have this query for todays records

SELECT COUNT(*) FROM `tblpatients` WHERE `Is_Deleted` = '0' AND `TimeStamp` <= NOW() AND `TimeStamp` >= ?????

I have a field in my table named TimeStamp format is 2014-09-20 12:11:20

Shafat Ahmad
  • 77
  • 2
  • 14

2 Answers2

0

Use strtotime() and date() function for getting yesterday. Example....

$now = date('Y-m-d h:i:s', time()); 
$yesterday = date('Y-m-d h:i:s', strtotime('yesterday'));
$sql = "SELECT COUNT(*) FROM `tblpatients` WHERE `Is_Deleted` = '0' AND `TimeStamp` <= '$now' AND `TimeStamp` >= '$yesterday'";

Also can use BETWEEN in query String. ie,.

$sql = "SELECT * FROM tblpatients WHERE Is_Deleted = '0' AND TimeStamp BETWEEN '$now' AND '$yesterday'";
MH2K9
  • 11,951
  • 7
  • 32
  • 49
0

Make your calculations based on CURDATE if you are selecting date only.

Hope the below Examples would help you

Today: WHERE timestamp >= CURDATE()
Yesterday: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE()
This month: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June): WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'

Please see this one too..

Community
  • 1
  • 1
Avinash Babu
  • 6,171
  • 3
  • 21
  • 26