Request to MySQL database. Sampling condition by date

Question

Hello, please tell me whether it is possible to make a request to the database of this type:

select all records with a date less than the specified one - a line like: "04/03/14 2:42 pm"

Now I make a request and then the condition:

$date = "03.04.14 14:42"; $query = "SELECT *, DATE_FORMAT( CONVERT_TZ(created,'+00:00','+04:00'), '%d.%m.%y %H:%i' ) as date_f FROM items ORDER BY created DESC"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { $i++; if ($i <= 500 AND $row['date_f'] <= $date){ echo $row['date_f']."<br />"; } }

But the fact is that the result of the condition displays only the dates for the current month:

 03.04.14 14:42 03.04.14 13:36 03.04.14 13:29 03.04.14 09:55 03.04.14 08:49 02.04.14 15:52 02.04.14 13:54 02.04.14 13:42 02.04.14 12:28 02.04.14 12:10 02.04.14 11:22 02.04.14 11:12 02.04.14 11:04 02.04.14 10:59

How can I make a SQL query or a condition so that the result displays ALL dates less than the original one?

1) Why not immediately make a query with a WHERE clause on the desired date range?
2) I see restrictions through PHP code, did you try to check the query itself, does it give the required range?
3) An explicit error is comparing dates as strings when the format% d.% M.% Y does not provide this.
3) the original date is a string, it cannot initially be a date
1) As far as I understand you have a datetime database, then contact directly: SELECT *, created as date_f FROM items WHERE created <'03 .04.14 14:42:00 'ORDER BY created DESC 2) Well, just make sure that the dates are coming other.
As I understand it, you think that, for example, "01/31/10 2:42 pm" <= "04/03/14 2:42 pm" will return true, but the strings are compared in the lexigraphic format, so 3> 0 and will return false.
This is not what you want, so usually the dates are either indicated as% y.% M.% D% H:% i, which matches the lexicographic one, or they are translated into a timestamp.

cheops cheops 18.1k 9 32 120 · Answer 1 · 2016-05-10T21:06:14

The mysql extension is deprecated and excluded from PHP 7. It is better to focus on the mysqli or PDO extensions when sampling.

In order to filter the date at the SQL query level, create the condition create < '2014-04-03 14:42:00'

 <?php try { $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]); $date = DateTime::createFromFormat('dmy H:i', "03.04.14 14:42"); $query = "SELECT * FROM history WHERE created < :created ORDER BY created DESC"; $itm = $pdo->prepare($query); $itm->execute(['created' => $date->format('Ymd H:i:s')]); while($item = $itm->fetch(PDO::FETCH_ASSOC)) { $date = DateTime::createFromFormat('Ymd H:i:s', $item['created']); echo $date->format('dmY H:i')."<br />"; } } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); }

The example involved the predefined class DateTime::createFromFormat to format a string with a date and time.

Good time, I have a similar problem - I did not begin to create a question here and so many similar things do not work for me: $ tmp = mysql_query ("SELECT * FROM stactions WHERE STR_TO_DATE (fdate, '% Y-% m =% d') <= STR_TO_DATE ('$ datenow', '% Y-% m =% d') AND STR_TO_DATE (tohda, '% Y-% m =% d') <= STR_TO_DATE ('$ datenow', '% Y-% m = % d ') ");
there are no errors) it simply returns an empty result as if there are no matches, although there are at least 2 lines with the values 2016-11-11 and 2016-10-8, and it simply gives an empty result
Then you don't need STR_TO_DATE () at all, compare them directly.

Request to MySQL database. Sampling condition by date

1 answer 1

More articles: