MySQL date query SQL statement summary (query one day, Query week, query one months of data)

My Date

The first date format we get is this: 2009-2-12 or 2009-3-3 or 2009-10-12, we can do this by combining SQL statements: (three examples)

The code is as follows	Copy Code
@1select * from user where birthday> ' 2009-2-3 ' and birthday< ' 2009-4-3 '

This allows us to find all user information that includes 2009-2-3 and is greater than 2009-2-3.

@2 If that's how we mix it.

The code is as follows	Copy Code
SELECT * from user where birthday> ' 2009-2-3 '

Then we'll find all the information that equals 2009-2-3 and is greater than the 2009-2-3 number.

@3 If we were such a combination

The code is as follows	Copy Code
SELECT * from user where birthday< ' 2009-2-3 '

Then we will check all the user information including 2009-2-3 and less than 2009-2-3.

This may be one of MySQL's own implementation mechanisms, not to write = number, = number is included in our written SQL statements. And in the combination of this SQL statement to pay attention to the "usage, if omitted" "Then we will eat big losses.

And the acquisition of the 2009-2-3 value.

Query Day:

The code is as follows	Copy Code
SELECT * FROM table where to_days (column_time) = To_days (now ()); SELECT * FROM table where date (column_time) = Curdate ();

Query Week:

The code is as follows	Copy Code
SELECT * FROM table where Date_sub (Curdate (), INTERVAL 7 day) <= DATE (column_time);

Enquiry One months:

The code is as follows	Copy Code
SELECT * FROM table where Date_sub (Curdate (), INTERVAL INTERVAL 1 MONTH) <= DATE (column_time);

Common functions for querying time periods using MySQL timestamp

Unix_timestamp (date)

Returns a UNIX timestamp (the number of seconds since ' 1970-01-01 00:00:00′gmt) If no parameter calls are invoked. If Unix_timestamp () is invoked with a date parameter, it returns the number of seconds from ' 1970-01-01 00:00:00′gmt. Date can be a date string, a DateTime string, a timestamp, or a number of local time in YYMMDD or YYYYMMDD format.

The code is as follows	Copy Code
Mysql> select Unix_timestamp (); -> 882226357 Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00′); -> 875996580

When Unix_timestamp is used in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transformation.

The code is as follows	Copy Code
From_unixtime (Unix_timestamp)

Returns the value represented by the Unix_timestamp parameter in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or a numeric context.

The code is as follows	Copy Code
Mysql> Select From_unixtime (875996580); -> ' 1997-10-04 22:23:00′ Mysql> Select From_unixtime (875996580) + 0; -> 19971004222300 From_unixtime (Unix_timestamp,format)

Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.

The code is as follows	Copy Code
Mysql> Select From_unixtime (Unix_timestamp (), '%Y%d%m%h:%i:%s%x '); -> ' 1997 23rd December 03:43:30 X '

Converts the date type data in the MySQL database into a TIMESTAMP form of UNIX by using the Unix_timestamp function: Select Unix_timestamp (' 2006-02-28′) testdate;