How to implement the date comparison, and display within 5 days, display the record within 10 days

Source: Internet
Author: User
Tags date comparison current time implement integer mysql php code query
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



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.