Home > Article > Backend Development > How to implement date comparison, cum_PHP tutorial
How to implement date comparison, cum_PHP tutorial
- WBOYOriginal
- 2016-07-13 10:53:32994browse
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 Focusing 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 and finally got the result. No. Dare to keep it to myself, post it for everyone to study, (although the technical content is not high, just don’t throw it at me, haha)
MYSQL’s TO_DAYS(DATE) function is explained like this:
Return DATE to 0 AD The total number of days, 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, then I Try to test with the above statement;
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 try 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 it occurred to me, hey, to_days(now()) produces an integer, so I can just operate on 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 result I wanted, haha The following is in PHP SELECT is used in the code to query
My habit of saving databases is to directly assign the value of DATEANDTIME with NOW(). There is no need to format it when displaying, it can be used directly after taking it out.
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, as follows It is a SQL query statement