CODEWITHSUNDEEP
phpmysql
Marius Prollak
Marius Prollak

Reputation: 368

Date Interval, count between - with own date/time format

I'm trying to get the count of visitors, for today and for yesterday individually.

My problem is that I'm using my own date and time format, it's saved in my DB like this: HH:MM DD.MM.YYYY, now to get the visitors for yesterday I tried this:

$sql="SELECT COUNT(*) as count FROM lst_visitors WHERE country = '$country' AND DATE(visits) = DATE_SUB(CURDATE(),INTERVAL 1 DAY)";

and for today:

$sql="SELECT COUNT(*) as count FROM lst_visitors WHERE country = '$country' AND DATE(visits) = DATE(NOW())";

But that just shows 0 for everything.

I then tried:

$today = date("H:i d.m.Y");
$sql="SELECT COUNT(*) as count FROM lst_visitors WHERE country = '$country' AND DATE(visits) = DATE($today())";

But that doesn't output anything.

Upvotes: 0

Views: 143

Answers (1)

Kermit
Kermit

Reputation: 34055

You should be storing this as a datetime type and you should convert them to such. In the mean time, you can use STR_TO_DATE:

SELECT STR_TO_DATE('15:50 27.03.2013', '%H:%i %d.%m.%Y')

Result

| STR_TO_DATE('15:50 27.03.2013', '%H:%I %D.%M.%Y') |
-----------------------------------------------------
|                      March, 27 2013 15:50:00+0000 |

Applying your fields:

$sql="SELECT COUNT(*) as count FROM lst_visitors WHERE country = '$country' 
          AND DATE(STR_TO_DATE(visits, '%H:%i %d.%m.%Y')) = DATE(NOW())";

$sql="SELECT COUNT(*) as count FROM lst_visitors WHERE country = '$country' 
          AND DATE(STR_TO_DATE(visits, '%H:%i %d.%m.%Y')) = DATE_SUB(CURDATE(),INTERVAL 1 DAY)";

Upvotes: 1

Related Questions