Mysql date query SQL statement Summary (query data for one day, one week, and one month)

Today, I want to create a program to query data for one day, one week, and one month. I will summarize and share these SQL date query statements.

My date

First, we get the date format as follows: 2009-2-12, 2009-3-3, or 2009-10-12. We can use this when combining SQL statements: (there are three examples)

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

In this way, we can find all the user information including 2009-2-3 and later than 2009-2-3.

@ 2 if we use this combination

The Code is as follows:	Copy code
Select * from user where birthday> '2014-2-3'

Then we will find all the information that is equal to 2009-2-3 and greater than 2009-2-3.

@ 3 if we use this combination

The Code is as follows:	Copy code
Select * from user where birthday <'2014-2-3'

Then we will query all user information that includes 2009-2-3 and less than 2009-2-3.

This may be an implementation mechanism of mysql itself. You do not need to write the = sign or the = sign, which is also included in the SQL statement we wrote. When combining this SQL statement, pay attention to the usage of ''. If you save'', we will suffer a big loss.

We can also obtain the value 2009-2-3.

Query for one 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 for one week:

The Code is as follows:	Copy code
Select * from table where DATE_SUB (CURDATE (), INTERVAL 7 DAY) <= date (column_time );

Query for one month:

The Code is as follows:	Copy code
Select * from table where DATE_SUB (CURDATE (), INTERVAL 1 MONTH) <= date (column_time );

Common functions used to query time periods using MYSQL timestamps

UNIX_TIMESTAMP (date)

If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a DATE string, a datetime string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format.

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

When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts the value directly without the implicit "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 number context.

The Code is as follows:	Copy code
Mysql> select FROM_UNIXTIME (875996580 ); -> '2017-10-04 22:23:00 ′ Mysql> select FROM_UNIXTIME (875996580) + 0; -> 19971004222300 FROM_UNIXTIME (unix_timestamp, format)

Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.

The Code is as follows:	Copy code
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x '); -> '2014 23rd December 03:43:30 x'

The UNIX_TIMESTAMP function is used to convert the date data in the MySQL database to an integer number in the form of unix timestamp: select UNIX_TIMESTAMP ('2017-02-28 ') testdate;