Mysql date comparison Function

There are a lot of date and time comparison functions in mysql. Next I will give you a brief introduction to some common date comparison functions and SQL statements, for your reference.

There are a lot of date and time comparison functions in mysql. Next I will give you a brief introduction to some common date comparison functions and SQL statements, for your reference.

The following query Selects all records. The value of date_col is within the last 30 days:

The Code is as follows:
Mysql> SELECT something FROM table WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30;

DAYOFWEEK (date)
Returns the index of the week of the date (1 = Sunday, 2 = Monday ,...... 7 = Saturday ). These index values correspond to the ODBC standard.

The Code is as follows:
Mysql> select DAYOFWEEK ('2017-02-03 '); -> 3

WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).

The Code is as follows:
Mysql> select WEEKDAY ('2017-10-04 22:23:00 '); -> 5 Mysql> select WEEKDAY ('2017-11-05 '); -> 2

Example 1

Assume that a table product has a field add_time, whose data type is datetime. Someone may write SQL as follows:

The Code is as follows:
Select * from product where add_time = '2017-01-12'

For such statements, if the format you store is YY-mm-dd, then OK. If the format you store is: 23:23:56, you will be miserable, this is the part where you can use the DATE () function to return the DATE, so this SQL statement should be processed as follows:

The Code is as follows:
Select * from product where Date (add_time) = '2017-01-12'

Next, what if you want to query the products you joined in February?

The Code is as follows:
Select * from product where date (add_time) between '2017-01-01 'and '2017-01-31'

You can also write:

The Code is as follows:
Select * from product where Year (add_time) = 2013 and Month (add_time) = 1

Share some common mysql date functions

NOW () returns the current date and time
CURDATE () returns the current date
CURTIME () returns the current time
DATE () extract the DATE part of the DATE or DATE/time expression
EXTRACT () returns a separate part of the date/time
DATE_ADD () Add the specified time interval to the date
DATE_SUB () minus the specified time interval from date
DATEDIFF () returns the number of days between two dates
DATE_FORMAT () display date/time in different formats