CODEWITHSUNDEEP
phpmysqldatestrtotime
it05jb
it05jb

Reputation: 3

Select between dates from table - strtotime?

I have been trying for a while, read countless stackoverflow answers and still cant crack it!

I have a table in my db with a field called dob. This field is currently just a TEXT field (but i have since tried changing it to a DATE field and still cant get it to work).

The DOB field's data is in this format (UK dates) - 22/05/2016.

Im trying to find out the number of users who's birthdays are between two dates.

For example, anyone who was born in the last two years:

$twoyearsago=date('d/m/Y', strtotime("-2 years"));
$today = date("d/m/Y");

$sql = mysql_query("SELECT * FROM users WHERE dob >= '" . $twoyearsago . "' AND date <= '" . $today . "' ORDER by id DESC");

I also tried:

  $sql = mysql_query("SELECT * FROM users WHERE dob BETWEEN '" . date('d-m-Y', strtotime($twoyearsago)) . "' AND '" . date('d-m-Y', strtotime($today)) . "'";

Hopefully you can see where me logic is and hoping you will see where im going wrong - any help would be appreciated.

Jack

Upvotes: 0

Views: 2835

Answers (4)

Peter
Peter

Reputation: 9123

Another method is to use DateTime and format the date before doing your query.

$begin = '10/02/2014';
$emd = '10/02/2015';

$beginDate = DateTime::createFromFormat('d/m/Y', $begin);
$emdDate = DateTime::createFromFormat('d/m/Y', $emd);

$stmt = "
SELECT
...
FROM users
WHERE birthday >= '".$beginDate->format('Y-m-d')."'
AND birthday <= '".$endDate->format('Y-m-d')."'
";

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

With STR_TO_DATE can you convert your date

NOTE: i have changed the Column type from TIMESTAMP to DATE, because in a TIMESTAMP you can store date before 1970-01-01.

SELECT STR_TO_DATE('22/05/2016','%d/%m/%Y');

sample

MariaDB [bb]> SELECT STR_TO_DATE('22/05/2016','%d/%m/%Y');
+--------------------------------------+
| STR_TO_DATE('22/05/2016','%d/%m/%Y') |
+--------------------------------------+
| 2016-05-22                           |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [bb]>

so you can change you Table

ALTER TABLE `users`
ADD COLUMN new_dob DATE;

UPDATE `users` SET new_dob = str_to_date(dob,'%d/%m/%Y');

** Verify the dates

ALTER TABLE `users`
DROP COLUMN dob;

ALTER TABLE `users`
CHANGE COLUMN `new_dob` `dob` DATE;

** CREATE an INDEX for perfomance **

ALTER TABLE `users`
ADD KEY (`dob`);

SELECT

SELECT * from `users` where dob between '2014-01-01' AND `2015-08-01';

Upvotes: 2

smozgur
smozgur

Reputation: 1812

You need to build your query by using actual date values, not string. So you need format YYYY-MM-DD in query - both side of the comparison.

Try following. 

$twoyearsago=date('Y-m-d', strtotime("-2 years")); 
$today = date("Y-m-d");

$sql = mysql_query("SELECT * FROM users WHERE STR_TO_DATE(dob, '%d/%m/%Y') >= '" . $twoyearsago . "' AND STR_TO_DATE(dob, '%d/%m/%Y') <= '" . $today . "' ORDER by id DESC");

STR_TO_DATE(dob, '%d/%m/%Y') makes sure your d/m/Y saved dob string value to be converted to date in the query that MySQL can understand and compare with the given YYYY-MM-DD values. Actually the proper way is creating a date field and transferring dob string values as date to this new field by using the same function unless you will always get the date values as string into the dob field.

Upvotes: 0

fvu
fvu

Reputation: 32953

The problem with many local date formats is that their lexical and chronological order are different (eg, 16-11-2016 comes after 11-12-2016 lexically, but before chronologically). That's why storing dates in string fields in some regional format is in most cases a bad idea: you will get sorting issues sooner or later.

Next, when specifying dates literally for MySQL, you have to respect certain formats, as explained in the documentation

Putting that into practice, the range variables should look something like this:

$today = date("Y-m-d");
$twoyearsago=date("Y-m-d", strtotime("-2 years"));

Then we use a built-in function str_to_date to convert the string column into a date that can be compared correctly:

SELECT * FROM users WHERE
STR_TO_DATE(dob, '%d/%m/%Y') between '$twoyearsago' and '$today'

This will work, but in the long run you're much better off converting that dob column into a real date format (as @BerndBuffen shows) as it's clearer, easier to internationalize and a lot better performing.

Sidenote: you are still using the long-deprecated mysql_ extension. You should really switch to either mysqli_ or PDO.

Upvotes: 1

Related Questions