How to search between two datetime in php MySQL?

Question

I have a table called reports in MySQL(MariaDB) . There is a one(out of 5) column named logdate which is is of type datetime .columns stores the the date and time (in 24hr format) . for ex here is sample value from that column

2021-04-10 09:35:00

I have to find all reports between a given date and time .

I get 4 variables from form data in PHP

$fromdate= $_POST['fromdate'];
$todate= $_POST['todate'];
$fromtime= $_POST['fromtime'];
$totime= $_POST['totime'];

$fromtime and $totime are just integers with value from 0-23 for hours. For example the condition may be like get all data between 4th April 2021 from 5 o'clock To 8 April 2021 18 o'clock i.e. From 2021-04-04 03:00:00 to 2021-04-08 18:00:00. There will be never condition on minutes and seconds .

My question is how to construct a datetime in PHP compatible with MySQL types so I can have good(efficient, there are millions of records in table ) search speed? for ex

$select = "select * from reports where logdate between ? and ? "; 

P.S: I tried saving date and time as integer as unixtime stamp. But when i convert from and to date received using strttotime() I facing time format issue due to bug in my code which so can use datetime only. If you have any suggestion to improve efficiency of DB please suggest.Thanks

Answer 1

Hi this link may be of help in optimizing date comparison MySQL SELECT WHERE datetime matches day (and not necessarily time)

This one below, will help you in formatting your strtotime() by using strptime()

https://www.php.net/manual/en/function.strptime.php

Also check your spelling or typo; you wrote "strttotime()" instead of "strtotime()" yours has an extra 't' in str"tto"time, it should be str"to"time, though without the double qoutes

Answer 2

Though I can't say for sure this is the most effective way but you can use hour(logdate) to compare with $fromdate and $todate

$select = "select * from reports where hour(logdate) between ? and ? "; 

But it will only compare hour part. Please mention how you are getting date part to compare?

Answer 3

It is not a good idea to make a calculation on a field in the WHERE CLAUSE. In this case MySQL / MariaDB must calculate the value from this field to comapare it to see if this ROW has this condition. So MySQL must read the whole table FULL TABLE SCAN and CANT use any INDEX.

A better way to do this is to store the calculation on fix site. Then MySQL calculated it only one time and can use a Index ( if there one) .

you can easy use a query like this:

$select = "SELECT * FROM reports where logdate between date(?) + INTERVAL ? HOUR AND date(?) + INTERVAL ? HOUR "; 

to test see:

SELECT date('2021-04-05') + INTERVAL 16 HOUR;
result:
2021-04-05 16:00:00

Answer 4

Here is what is working for me after using Bernds solution . I constructing datetime string in php

$fromstr ="$fromdate"." "."$fromtime".":00:00";
$tostr="$todate"." "."$totime".":00:00";

here is my query looks like for date of 7th April to 10th April

$ select = "SELECT * FROM reports where logdate >= '$fromstr' and logdate <= '$tostr' order by logdate";

after echoing it

"SELECT * FROM reports where logdate >= '2021-04-07 3:00:00' and logdate <= '2021-04-10 5:00:00' order by logdate";```

However I am not sure if can use index for logdate column and utilize it with above query.