How to implement date comparison and display records within 5 days and within 10 days

How to implement date comparison and display records within 5 days and within 10 days_PHP Tutorial

WBOY

Release： 2016-07-13 17:21:19

Original

1003 people have browsed it

I posted a post a few days ago about displaying this kind of date comparison within 5 days.

http://www.oso.com.cn/forum/read.php?theme_id=7459

The main argument is focused on SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 5;. I tried it for a long time, but the result was still wrong. I looked at the MYSQL function today. , finally the results came out, I don’t dare to keep them to myself, I posted them for everyone to study, (although the technical content is not high, just don’t throw me the persimmons, haha)

MYSQL's TO_DAYS(DATE) function is explained like this:
Returns the total number of days from DATE to AD 0. I tested it
mysql>select to_days(now(0));
+--------------------------+
| TO_DAYS(NOW()) |
+------- ------------------+
| 730839 |
+-------------------- ------+

What comes out is the total number of days from the current time to AD 0, and then I try to use the above statement to test;

mysql>select TO_DAYS(NOW()) - TO_DAYS(date_col) <= 5;
The result appears:
ERROR 1054:Unknown column date_col in field first

This way is blocked, so I tried to put the 5th generation directly into date_col

mysql>select to_days(now()) - to_days(5);
The result appears:
+---------------------- -----+
|to_days(now()) - to_days(5)|
+--------------------- ---+
| NULL |
+--------------------------+

Huh? No way? This doesn’t work either?
I then try the command
mysql>select. . . .

Suddenly, I suddenly thought, hey, to_days(now()) comes out as an integer. I can just do the operation with the integer directly. Why bother to do to_days(date) again? Try it now

mysql>select to_days(now()) - 5;
+--------------------------+
| to_days(now()) -5 |
+--------------------------+
| 730834 |
+ --------------------------+

OK, long live, I finally got the results I wanted, haha. The following is the SELECT query in the PHP code

My habit of saving databases is to use NOW() to directly assign DATEANDTIME values. There is no need to format when displaying, just take it out and use it.

The following is a partial structure of one of my libraries
CREATE TABLE infomess (
infoid int(11) 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 want to query the records within 5 days. The following is the SQL query statement
$sql="select * from infomess where to_days(dateandtime) >= (to_days (now()) - 5) order by infoid desc limit $offset,$psize";

Just where to_days(dateandtime) >= (to_days(now()) - 5) is enough. The following is another one. The 5 here can be set as a variable

where to_days(dateandtime) >= (to_days(now()) - $limitdays)

Then $limitdays can be passed by GET method (mostly by GET method)

Just follow ?limitdays=5 in your PHP. It will be the same within 10 days. Just change $limitdasy to 10.

The above results are obtained by using MYSQL function. The above results have been tested. Because of the rush of time, if there are any problems with the code, please leave a comment. Thank you

Some friends also said to use UNIX stamps to get such results. Who has written such code and posted it for everyone’s reference and comparison. You can also test and judge the efficiency of PHP function or MYSQL function. High