Go to: MySQL date/time functions

You can check it later.

I,MySQLGet current date and time

 

Now (): Obtain the current date + time (date + time) function:

Mysql> select now ();

+ --------------------- +

| Now () |

+ --------------------- +

| 10:26:23 |

+ --------------------- +

1 row in SET (0.00 Sec)

The same functions include current_timestamp () and localtime (), but now () is the easiest to remember. Therefore, we recommend that you use them.

 

Sysdate (): The datetime function is similar to now (). The difference is that now () is obtained at the execution start value, and sysdate () dynamically obtains the value during function execution.

You can see the following example:

Mysql> select now (), sleep (3), now ();

+ --------------------- + ---------- + --------------------- +

| Now () | sleep (3) | now () |

+ --------------------- + ---------- + --------------------- +

| 10:51:43 | 0 | 10:51:43 |

+ --------------------- + ---------- + --------------------- +

1 row in SET (3.02 Sec)

 

Mysql> select sysdate (), sleep (3), sysdate ();

+ --------------------- + ---------- + --------------------- +

| Sysdate () | sleep (3) | sysdate () |

+ --------------------- + ---------- + --------------------- +

| 10:52:09 | 0 | 10:52:12 |

+ --------------------- + ---------- + --------------------- +

1 row in SET (3.00 Sec)

As you can see, although sleep lasts for 3 seconds, the time values of the now () function are the same. The difference between the time values obtained by the sysdate () function is 3 seconds.

 

 

You can also set only the current date or time.

Curdate ():Get current date

 

Mysql> select curdate ();

+ ------------ +

| Curdate () |

+ ------------ +

| 2011-03-01 |

+ ------------ +

1 row in SET (0.01 Sec)

 

Curtime (): Get the current time function

 

Mysql> select curtime ();

+ ----------- +

| Curtime () |

+ ----------- +

| 10:55:12 |

+ ----------- +

1 row in SET (0.00 Sec)

 

II,MySQLExtract Date and Time Extract

 

With this function, we can extract the desired part from a time, such

Mysql> set @ Ct = '2017-03-01 11:16:14. 123 ';

Query OK, 0 rows affected (0.01 Sec)

Set the variable CT to a specific time value, accurate to subtle

 

Get its date value

Mysql> select date (@ CT );

+ ------------ +

| Date (@ CT) |

+ ------------ +

| 2011-03-01 |

+ ------------ +

1 row in SET (0.00 Sec)

 

View the quarter of the date

Mysql> select quarter (@ CT );

+ -------------- +

| Quarter (@ CT) |

+ -------------- +

| 1 |

+ -------------- +

1 row in SET (0.00 Sec)

 

View the week of the current year

Mysql> select Week (@ CT );

+ ----------- +

| Week (@ CT) |

+ ----------- +

| 9 |

+ ----------- +

1 row in SET (0.00 Sec)

In addition, year (), Day (), hour (), minute (), second () and so on are also described here.

 

Similar functions can be implemented using extract (). The syntax format is extract (year from @ CT ),

The disadvantage is that you need to press the keyboard several times.

 

DayofFunction:

Dayofweek (), dayofmonth (), and dayofyear () return the positions in the next week, January, and one year respectively.

Mysql> select dayofweek (@ CT );

+ ---------------- +

| Dayofweek (@ CT) |

+ ---------------- +

| 3 |

+ ---------------- +

1 row in SET (0.00 Sec)

Note: In fact, July 22, March 1 is Tuesday, but the number 3 is returned because it is counted from Sunday (1 = Sunday, 2 = Monday ,...)

 

Mysql> select dayofmonth (@ CT );

+ ----------------- +

| Dayofmonth (@ CT) |

+ ----------------- +

| 1 |

+ ----------------- +

1 row in SET (0.00 Sec)

 

Mysql> select dayofyear (@ CT );

+ ---------------- +

| Dayofyear (@ CT) |

+ ---------------- +

| 60 |

+ ---------------- +

1 row in SET (0.00 Sec)

 

Week () function

View the week of the current year

Mysql> select weekofyear (@ CT );

+ ----------------- +

| Weekofyear (@ CT) |

+ ----------------- +

| 9 |

+ ----------------- +

1 row in SET (0.00 Sec)

 

 

Returns the week and month names.

Dayname ()-The calculation date is the day of the week.

Mysql> select dayname (@ CT );

+ -------------- +

| Dayname (@ CT) |

+ -------------- +

| Tuesday |

+ -------------- +

1 row in SET (0.02 Sec)

 

Monthname ()-Date: January

Mysql> select monthname (@ CT );

+ ---------------- +

| Monthname (@ CT) |

+ ---------------- +

| March |

+ ---------------- +

1 row in SET (0.00 Sec)

 

Last_day (): Returns the last day of the month.

Mysql> select now ();

+ --------------------- +

| Now () |

+ --------------------- +

| 13:15:00 |

+ --------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select last_day (now ());

+ ----------------- +

| Last_day (now () |

+ ----------------- +

| 2011-03-31 |

+ ----------------- +

1 row in SET (0.00 Sec)

This function calculates the number of days of the current month.

Mysql> select now (), Day (last_day (now ()));

+ --------------------- + ---------------------- +

| Now () | day (last_day (now () |

+ --------------------- + ---------------------- +

| 13:17:12 | 31 |

+ --------------------- + ---------------------- +

1 row in SET (0.00 Sec)

 

 

Iii. MySQL Date and Time Calculation Functions

Date_add (): Adds a time interval for the date.

The specific syntax is date_add (@ CT, interval num year/quarter/month/week/day/hour/minute/secont/microsecond );

Note: This function does not change the actual value of the variable @ CT.

Mysql> set @ Ct = now ();

Query OK, 0 rows affected (0.00 Sec)

 

Mysql> select @ CT;

+ --------------------- +

| @ CT |

+ --------------------- +

| 15:09:16 |

+ --------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select date_add (@ CT, interval 1 day );

+ ------------------------------ +

| Date_add (@ CT, interval 1 day) |

+ ------------------------------ +

| 15:09:16 |

+ ------------------------------ +

1 row in SET (0.00 Sec)

 

Mysql> select @ CT;

+ --------------------- +

| @ CT |

+ --------------------- +

| 15:09:16 |

+ --------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select date_add (@ CT, interval 1 week );

+ ------------------------------- +

| Date_add (@ CT, interval 1 week) |

+ ------------------------------- +

| 15:09:16 |

+ ------------------------------- +

1 row in SET (0.00 Sec)

 

Similar functions include adddate (), addtime (), and other functions. The corresponding function is date_sub (), which is called date subtraction.

 

Alternative date functions

Period_add (p, n): date plus/minus n months. The format of P should be yyyymm or yymm.

Period_diff (P1, P2): Date p1-p2, returns n months

 

Mysql> select period_add (), period_add (201103,-2 );

+ ---------------------- + ----------------------- +

| Period_add () | period_add (201103,-2) |

+ ---------------------- + ----------------------- +

| 201105/201101 |

+ ---------------------- + ----------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select period_diff ('20140901', '20160901 ');

+ -------------------------------- +

| Period_diff ('20140901', '20160901') |

+ -------------------------------- +

| 2 |

+ -------------------------------- +

1 row in SET (0.00 Sec)

 

Date and time subtraction Function

Datediff (date1, date2): Two date1-date2 dates

Mysql> select datediff ('2017-03-09 ', '2017-03-01 ');

+ ------------------------------------- +

| Datediff ('2017-03-09 ', '2017-03-01') |

+ ------------------------------------- +

| 8 |

+ ------------------------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select datediff ('2017-03-01 ', '2017-03-09 ');

+ ------------------------------------- +

| Datediff ('2017-03-01 ', '2017-03-09') |

+ ------------------------------------- +

|-8 |

+ ------------------------------------- +

1 row in SET (0.00 Sec)

 

Timediff (time1, time2): Two time subtraction

Mysql> select timediff ('2017-03-03 15:33:00 ', '2017-03-02 15:33:59 ');

+ --------------------------------------------------------- +

| Timediff ('2017-03-03 15:33:00 ', '2017-03-02 15:33:59') |

+ --------------------------------------------------------- +

| 23:59:01 |

+ --------------------------------------------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select timediff ('15: 33: 00', '15: 33: 59 ');

+ --------------------------------- +

| Timediff ('15: 33: 00', '15: 33: 59') |

+ --------------------------------- +

|-00:00:59 |

+ --------------------------------- +

1 row in SET (0.00 Sec)

 

 

Iv. MySQL Date and Time conversion functions

 

Time_to_sec (time): Time-> Second Conversion Function

Sec_to_time (Num): Second --> time Conversion Function

 

Mysql> select time_to_sec ('01: 00: 00 ');

+ ------------------------- +

| Time_to_sec ('01: 00: 00') |

+ ------------------------- +

| 1, 3600 |

+ ------------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select sec_to_time (3600 );

+ ------------------- +

| Sec_to_time (3600) |

+ ------------------- +

| 01:00:00 |

+ ------------------- +

1 row in SET (0.00 Sec)

 

 

To_days (date): Date --> the start date of the day conversion function is 0000-00-00.

From_days (Num): Day --> converts a number to a specific date.

Mysql> select to_days ('2017-03-01 ');

+ ----------------------- +

| To_days ('2017-03-01 ') |

+ ----------------------- +

| 1, 734562 |

+ ----------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select from_days (734562 );

+ ------------------- +

| From_days (734562) |

+ ------------------- +

| 2011-03-01 |

+ ------------------- +

1 row in SET (0.00 Sec)

 

 

Str_to_date (STR, date): String --> date Conversion Function

It can convert disordered characters into date formats.

Mysql> select str_to_date ('01. 03.2011 ',' % m. % d. % y ');

+ --------------------------------------- +

| Str_to_date ('01. 03.2011 ',' % m. % d. % y') |

+ --------------------------------------- +

| 2011-01-03 |

+ --------------------------------------- +

1 row in SET (0.00 Sec)

 

Mysql> select str_to_date ('2014/1/123', '% m/% d/% y ');

+ --------------------------------------- +

| Str_to_date ('2014/1/123', '% m/% d/% y') |

+ --------------------------------------- +

| 2011-01-03 |

+ --------------------------------------- +

1 row in SET (0.00 Sec)

 

 

 

Exercises:

Taking the table centralmobile_logs as an example, the table currently has a total of more than 2.7 million data records

Mysql> select count (*) from centralmobile_logs;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 2725403 |

+ ---------- +

1 row in SET (0.00 Sec)

 

Make some statistics on it now

 

Query the total amount of data in the past 30 days

Mysql> select count (*) from centralmobile_logs where to_days (curdate ()-to_days (create_time) <= 30;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 2367518 |

+ ---------- +

1 row in SET (3.38 Sec)

 

Mysql> select count (*) from centralmobile_logs where datediff (curdate (), create_time) <= 30;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 2367518 |

+ ---------- +

1 row in SET (3.29 Sec)

 

View data on the first day of each month

Mysql> select count (*) from centralmobile_logs where dayofmonth (create_time) = 1;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 161293 |

+ ---------- +

1 row in SET (3.14 Sec)

 

View data before January 11, January 31

Mysql> select count (*) from centralmobile_logs where create_time <= '2017-01-31 00:00:00 ';

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 413797 |

+ ---------- +

1 row in SET (0.17 Sec)

 

View data for February years

Mysql> select count (*) from centralmobile_logs where monthname (create_time) = 'february 'and year (create_time) = 2011;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 2149284 |

+ ---------- +

1 row in SET (3.94 Sec)

 

View the accumulated data of every Sunday in 11 years

Mysql> select count (*) from centralmobile_logs where dayname (create_time) = 'sunday' and year (create_time) = 2011;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 479033 |

+ ---------- +

1 row in SET (3.88 Sec)

 

View the total data inserted at every day

Mysql> select count (*) from centralmobile_logs where time (create_time) = '00: 00: 00 ';

+ ---------- +

| Count (*) |

+ ---------- +

| 37 |

+ ---------- +

1 row in SET (3.99 Sec)