MySQL date query operation copy

Today

Select * from table name where to_days (time field name) = to_days (now ());

Yesterday

Select * from table name where to_days (now ()-to_days (time field name)
<= 1

If it was 7 days ago, it would be the date_add function.

Select * from table name where date_sub (curdate (), interval 7 day)
<= Date (time field name)

Last 30 days ago

Select * from table name where date_sub (curdate (), interval 30 day)
<= Date (time field name)

This month

Select * from table name where date_format (time field name, '% Y % m') =
Date_format (curdate (), '% Y % m ')

Last month

Select * from table name where period_diff (date_format (now (), '% Y % m'
), Date_format (time field name, '% Y % m') = 1

// Converts the time to year, month, day, hour, minute, and second.
Select date_format (now (), '% Y % m % d % H % I % s ')
// Convert the time to year, month, and day
Select date_format (now (), '% Y % m % D ')
// At this time last year
Select date_add (now (), interval-1 year)
// At this time last month
Select date_add (now (), interval-1 month)
// At this time yesterday
Select date_add (now (), interval-1 day)
// One hour ago
Select date_add (now (), interval-1 hour)
// One minute ago
Select date_add (now (), interval-1 minute)
// One second ago
Select date_add (now (), interval-1 second)
// Yesterday (year, month, and day)
Select date_format (date_add (now (), interval 1 day), '% Y % m % D ')

// The first and last days of the last month
Select date_sub (date_format (now (), '% Y % m % D'), interval extract (day from now ()-1 day), interval 1 month );
Select date_sub (date_format (now (), '% Y % m % D'), interval extract (day from now () Day), interval 0 month );

// A string
Select date_format (date_add ('000000', interval 20 minute), '% Y % m % D ')
// Week
Select weekofyear (now ())
Select weekofyear ('20140901 ')

InMySQLThe string in the '20170901' and '20160901' formats is converted as the date type.

In MySQLOracleTo_char (Num, format) function of, so it is necessary to deal with the case that 0 is involved before the number.

For example, select left (Concat ('00'), @ num), 3) will display a string with three digits. When @ num = 1, 001 is displayed, and 123 is displayed.

Concat (Year (A. createtime), left (Concat ('0', weekofyear (A. createtime), 2 ))

AndFrom_unixtime, which can be converted to date by number