get values from table only for a specific day in mysql

Question

i can't get the value inside my database for a specific date

sample values:

$from= 12/15/2012 $to= 12/15/2012
$from= 12/16/2012 $to= 12/16/2012
$from= 12/17/2012 $to= 12/17/2012

php script:

$query=" select * from call where (date_time BETWEEN '$from' AND '$to') ORDER BY date_time DESC";

the value on my table which i'm getting my dates from has time in it

ex values on my data base:

12/15/2012 2:51
12/15/2012 2:21
12/15/2012 2:55
12/15/2012 3:12
12/16/2012 2:51
12/16/2012 2:21
12/17/2012 2:55
12/17/2012 3:12

when i search for from 12/15/2012 to 12/15/2012 it does not display the values for

12/15/2012

but if i search for

from 12/15/2012 to 12/16/2012

it displays not only the values for 12/15/2012 but also for 12/16/2012

what should i do so that i can enclose all the values only for that specific date?

I would suggest storing your dates as timestamps so that they are much easier to search and manipulate. — SISYN, Dec 29 '12 at 21:37

score 4 · Answer 1 · edited May 23 '17 at 10:27

4

First of all, you should store your time values in a DATETIME (or possibly TIMESTAMP) column. Then follow the answers given to this question.

However, if you really insist on doing this with strings, you could always use a LIKE comparison:

SELECT * FROM `call` WHERE date_time LIKE '12/15/2012 %'

(demo on sqlize.com)

Of course, this only works for finding all records for a single day (or, with appropriate modification, for a single hour / minute / month / year / etc.). If you need more complicated selection criteria, I'd strongly suggest switching to a real DATETIME column.

edited May 23 '17 at 10:27

Community

1
1

answered Dec 29 '12 at 21:50

Ilmari Karonen

49,047
9
93
153

I think he want the query for `to and from` For both, exact similar dates and dates in between . `Like` will do for only exact similar dates. So i guess he can get his results by using `<= ` operator (using string to date function) – soft genic Dec 29 '12 at 21:53
@softgenic: Maybe, and if so, he really should use a `DATETIME` column (or, at the very least, [ISO-style date formatting](http://en.wikipedia.org/wiki/ISO_8601)). But all his examples show the single days. – Ilmari Karonen Dec 29 '12 at 22:00

score 3 · Answer 2 · edited Dec 29 '12 at 22:24

Assuming you are storing dates as datetime field, you should convert data to Date type.

When you write:

$from= 12/15/2012

PHP do a division: 12 / 15 and then ( 12 / 15 ) / 2012

An approach will be declare $from as string and cast to date in query:

$from= '12/15/2012'
...
"select * from call where (date_time BETWEEN STR_TO_DATE('$from', '%m/%d/%Y') ..."

The right query for a index friendly plan is:

select * 
from   `call` 
where  date_time >= STR_TO_DATE('$from', '%m/%d/%Y') and 
       date_time < date_add( STR_TO_DATE('$to', '%m/%d/%Y'), interval 1 day)

See it in sqlfiddle

Thats what i was talking about :) – soft genic Dec 29 '12 at 22:12 — soft genic, Dec 29 '12 at 22:12

get values from table only for a specific day in mysql

2 Answers2

Linked