How to compare dates and display 10 days in 5 days

Source: Internet
Author: User
Tags mysql functions
The important argument is that it is concentrated on SELECTsomethingFROMtableWHERETO_DAYS (NOW ()-TO_DAYS (date_col) = 5; I tried it for a long time, but the results still failed. today I read MYSQL functions, finally, I came up with the results and did not dare to exclusively share them. I posted them for your research,

The important argument is to focus on SELECT something FROM table
WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 5; on my experiment for a long time, the results still failed. today I read MYSQL functions and finally came up with the results, I don't dare to share it with others. I will post it for your research. (although the skill content is not high, just don't throw my persimmon)

The TO_DAYS (DATE) function of MYSQL is clarified as follows:
Returns the total number of days from DATE to 0 A.M. I tested it.
Mysql> select to_days (now (0 ));
--------------------------
| TO_DAYS (NOW () |
--------------------------
| 1, 730839 |
--------------------------

The current time interval is the total number of days in 0 years. then I try to use the above statement to test;

Mysql> select TO_DAYS (NOW ()-TO_DAYS (date_col) <= 5;
Results:
ERROR 1054: Unknown column 'date _ col' in 'Field first'

This cannot be done, so I try to direct Generation 5 to date_col.

Mysql> select to_days (now ()-to_days (5 );
Results:
---------------------------
| To_days (now ()-to_days (5) |
---------------------------
| NULL |
---------------------------

Ah? No, right? That's not the case?
I will try again
Mysql> select ....

Suddenly, I think that to_days (now () is an integer, so I can directly perform an integer operation. why to_days (date? Experiment now

Mysql> select to_days (now ()-5;
--------------------------
| To_days (now ()-5 |
--------------------------
| 1, 730834 |
--------------------------


OK, long live, and finally get the results I want.

My habit of storing databases is that DATEANDTIME can be directly assigned values using NOW (). It can be used directly after it is retrieved without going through the pattern,

Below is a part of my library structure
Create table infomess (
Infoid int (11) not null auto_increment,
Topic varchar (255) not null,
......
Email varchar (50 ),
Dateandtime datetime DEFAULT '2017-00-00 00:00:00 'not null,
Primary key (infoid)
);


Here, the DATEANDTIME is the date pattern of the scale, and then I want to query the records within five days. below is the SQL query statement
$ SQL = 'select * from infomess where to_days (dateandtime) >=( to_days (now ()-5) order by infoid desc limit $ offset, $ psize ';

A where to_days (dateandtime) >=( to_days (now ()-5) is enough for another variable. here, 5 can be set as a variable.

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

Then $ limitdays can be passed using the GET method (most of them are transmitted using the GET method)

Keep up with your PHP? Limitdays = 5. The same is true for 10 days. Change $ limitdasy to 10.

The above is the result of using MYSQL functions. all the above results have been tested. due to the rush of time, if the code has any title, please post it with me. thank you.


Another friend said that the UNIX stamp was used to get such a result. which one has written such a code and posted it for your reference, you can also test whether the PHP function or MYSQL function is highly efficient.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.