Compare | show
The main argument is to focus on the select something from table
WHERE To_days (now ())-To_days (Date_col) <= 5; On, I experimented for a long time, the result or error, today saw the function of MySQL, finally came out of the results, dare not to enjoy, posted out for everyone to study, (although the technical content is not high, do not throw me persimmon on the line, hehe)
The To_days (DATE) function of MySQL illustrates this:
Returns date to the total number of days of 00, I tested
Mysql>select To_days (now (0));
+--------------------------+
| To_days (Now ()) |
+--------------------------+
| 730839 |
+--------------------------+
Coming out is the current time distance of the total day of 00, then I try to use the above sentence test;
Mysql>select To_days (now ())-To_days (Date_col) <= 5;
The result appears:
ERROR 1054:unknown column ' Date_col ' in ' field a '
Blocked, I'm going to try and get 5 generations straight to Date_col.
Mysql>select To_days (now ())-to_days (5);
The result appears:
+---------------------------+
|to_days (now ())-to_days (5) |
+---------------------------+
| NULL |
+---------------------------+
Ah? No way? That's not going to work, is it?
I'll try the order.
Mysql>select .....
Suddenly think, hey, To_days (now ()) out is an integer, I directly with the integer operation on the line, why again to_days (date)? Experiment now.
Mysql>select To_days (now ())-5;
+--------------------------+
| To_days (now ())-5 |
+--------------------------+
| 730834 |
+--------------------------+
OK, Long live, finally got the result that I want, hehe the following is in the PHP code with select query
My database has always been the habit of DateAndTime with now () direct assignment, the display of the time without formatting, directly out to use,
Here's a partial structure of one of my libraries
CREATE TABLE infomess (
infoid Int (one) not NULL auto_increment,
Topic varchar (255) Not NULL,
......
Email varchar (50),
DateAndTime datetime DEFAULT ' 0000-00-00 00:00:00 ' not NULL,
PRIMARY KEY (infoid)
);
The dateandtime here is the standard date format, and then I'm going to query the records within 5 days, the following is the SQL query statement
$sql = "SELECT * from Infomess where To_days (dateandtime) >= (To_days (Today ())-5) Order BY infoid desc limit $offset, $psi Ze ";
You need a where to_days (DateAndTime) >= (Now ()-5) is enough. The following is another, where 5 can be set to a variable
where To_days (DateAndTime) >= (To_days (now ())-$limitdays)
$limitdays can then be passed in Get mode (mostly with get-way passes)
Behind your PHP? Limitdays=5 on the line. Show 10 days is the same, $limitdasy changed to 10 on the line
The above is to use the MySQL function to get such results, the results have been tested, because the time is hurried, if the code has any problems, please post, thank you
There are friends that use the Unix stamp to get such a result, I would like to ask who wrote this code, paste point out for everyone to compare, you can test to determine the PHP function or MySQL function to achieve high efficiency