I. Basic Introduction
1. to_days (date)
Returns the number of days (the number of days from 0 years ):
Mysql> select to_days (20110311 );
+ ------------------- +
| To_days (20110311) |
+ ------------------- +
| 1, 734572 |
+ ------------------- +
1 row in SET (0.00 Sec)
Mysql> select to_days ('2017-01-19 ');
+ ----------------------- +
| To_days ('2017-01-19 ') |
+ ----------------------- +
| 1, 725390 |
+ ----------------------- +
1 row in SET (0.00 Sec)
To_days () does not intend to use values that appear before the Gregorian Calendar (that is, the current Gregorian calendar) (1582), because it does not consider the number of days lost when the calendar changes.
2. from_days (N)
Given a number of days n, return a date value:
Mysql> select from_days (725390 );
+ ------------------- +
| From_days (725390) |
+ ------------------- +
| 1986-01-19 |
+ ------------------- +
1 row in SET (0.00 Sec)
3. Now ()
Get the current date
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 15:49:06 |
+ --------------------- +
1 row in SET (0.00 Sec)
4. date_format (date, Format)
Format date
Mysql> select date_format (now (), '% Y-% m-% D ');
+ ------------------------------- +
| Date_format (now (), '% Y-% m-% D') |
+ ------------------------------- +
| 2011-03-11 |
+ ------------------------------- +
1 row in SET (0.01 Sec)
II. Application
1. Search for all records within today
Select distinct something from table where timestamp> date_format (now (), '% Y-% m-% D ')
2. Search for all records within the current 12 hours
Select distinct something from table where to_days (now ()-to_days (timestamp)