The time functions commonly used in MySQL

Time function:

Now: Current Time band format

Mysql> Select Now (); +---------------------+| Now ()               |+---------------------+| 2015-09-06 10:47:33 |+---------------------+1 row in Set (0.00 sec)

To_days: Given a date , returns a number of days, starting from year 0.

Mysql> Select To_days (now ()); +----------------+| To_days (now ()) |+----------------+|         736212 |+----------------+1 row in Set (0.00 sec)

Number of bars inserted today:

Mysql> Select COUNT (*) from black_list where To_days (now ())-To_days (CTime) <1;+----------+| COUNT (*) |+----------+|    38089 |+----------+1 row in Set (1.93 sec)

Number of years function:

Days function:

Week function:

What day is today:

DAYOFWEEK  1 = "Sunday; 2 =" Monday; 3 = "Tuesday ... 7= "Saturday mysql> Select DAYOFWEEK (now ()); +------------------+| DAYOFWEEK (now ()) |+------------------+|                1 |+------------------+1 row in Set (0.00 sec) Weekday 0 = "Monday; 1 =" Tuesday; 2 = "Wednesday ... 6= "Sunday mysql> Select Weekday (now ()); +----------------+| Weekday (now ()) |+----------------+|              6 |+----------------+1 row in Set (0.00 sec)

Today's date: DayOfMonth:

Mysql> Select DayOfMonth (now ()); +-------------------+| DayOfMonth (now ()) |+-------------------+|                 6 |+-------------------+1 row in Set (0.00 sec)

Today is the first day of the year: DayOfYear

Mysql> Select DayOfYear (now ()); +------------------+| DayOfYear (now ()) |+------------------+|              249 |+------------------+1 row in Set (0.00 sec)

Query Month:

Mysql> Select Month (now ()); +--------------+| Month (now ()) |+--------------+|            9 |+--------------+1 row in Set (0.00 sec)

Week of the year (starting from 0) there is a second parameter 1 starting from 1: Week (Now (), 1)

Mysql> Select Week ("2015-01-10"); +--------------------+| Week ("2015-01-10") |+--------------------+|                  1 |+--------------------+1 row in Set (0.00 sec) mysql> Select Week ("2015-01-11"); +--------------------+| Week ("2015-01-11") |+--------------------+|                  2 |+--------------------+1 row in Set (0.00 sec) mysql> Select Week ("2015-01-1"); +-------------------+| Week ("2015-01-1") |+-------------------+|                 0 |+-------------------+

　　

Day of the Week in English:

Mysql> Select Dayname (now ()); +----------------+| Dayname (now ()) |+----------------+| Sunday         |+----------------+1 row in Set (0.00 sec)

Month in English

Mysql> Select MonthName (now ()); +------------------+| MonthName (now ()) |+------------------+| September        |+------------------+1 row in Set (0.00 sec)

　　

Quarter (1-4)

Mysql> Select QUARTER (now ()); +----------------+| QUARTER (now ()) |+----------------+|              3 |+----------------+1 row in Set (0.00 sec)

Month and day hours and seconds:

Mysql> Select Year (now ()); +-------------+| Year (now ()) |+-------------+|        |+-------------+1 Row in Set (0.00 sec) mysql> Select Month (now ()); +--------------+| Month (now ()) |+--------------+|            9 |+--------------+1 row in Set (0.00 sec) mysql> Select Day (now ()); +------------+| Day (now ()) |+------------+|          6 |+------------+1 row in Set (0.00 sec) mysql> Select Hour (now ()); +-------------+| Hour (now ()) |+-------------+|          |+-------------+1 Row in Set (0.00 sec) mysql> Select Minute (now ()); +---------------+| Minute (now ()) |+---------------+|            |+---------------+1 Row in Set (0.00 sec) mysql> Select Second (now ()); +---------------+| Second (now ()) |+---------------+|            |+---------------+1 Row in Set (0.00 sec)

Add Date: First parameter time (2015-09-06-11:46:37), second parameter Interval 2 year, Interval 2 week, Interval 2 day, Interval 2 hour, minute, second

Mysql> Select Date_add (now (), Interval 2); +----------------------------------+| Date_add (now (), Interval 2) |+----------------------------------+| 2017-09-06 11:46:37              |+----------------------------------+1 row in Set (0.00 sec)

　　

Date formatting:

Date_format (Date,format)
Formats the date value according to the format string. The following modifiers can be used in the format string:%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (00 ... 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ... 59)
%s seconds (00 ... 59)
%p am or PM
%w days in one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
Percent of a text "%".

　

Mysql> Select Date_format (now (), "%M"); +-------------------------+| Date_format (now (), "%M") |+-------------------------+| September |+-------------------------+1 row in Set (0.00 sec) mysql> Select Date_format (now (), "%m"); +----- --------------------+| Date_format (now (), "%m") |+-------------------------+| |+-------------------------+1 Row in Set (0.00 sec) mysql> Select Date_format (now (), "%d"); +----- --------------------+| Date_format (now (), "%d") |+-------------------------+| 6th |+-------------------------+1 row in Set (0.00 sec) mysql> Select Date_format (now (), "%d"); +----- --------------------+| Date_format (now (), "%d") |+-------------------------+| |+-------------------------+1 Row in Set (0.00 sec) mysql> Select Date_format ("2015-8-28", "%d"); +-------------------------------+| Date_format ("2015-8-28", "%d") |+-------------------------------+| |+-------------------------------+1 Row in Set (0.00 sec) mysql> Select Date_format ("2015-8-28", "%m-%d"); +----------------------------------+ | Date_format ("2015-8-28", "%m-%d") |+----------------------------------+| 08-28 |+----------------------------------+1 row in Set (0.00 sec)

　　

adjourned

The time functions commonly used in MySQL