MySQL Learning record-operating time data

1. Time function to return date

Date_add (): You can increase/decrease any time interval for a specified date. The following example increases the current date by one months and decreases by one months

Mysql> SelectDate_add (current_date(), Interval1 Month) asAdd_one_month;+---------------+|Add_one_month|+---------------+|  .- A- -    |+---------------+1Rowinch Set(0.00sec) MySQL> SelectDate_add (current_date(), Interval-1 Month) asReduce_one_month;+------------------+|Reduce_one_month|+------------------+|  .-Ten- -       |+------------------+1Rowinch Set(0.00Sec

Format: Date_add (' Time to modify ', interval number of interval types), below shows some common time interval types

Interval name	Describe
Second	Number of seconds
Minute	Number of minutes
Hour	Number of hours
Day	Days
Month	Month
Year	Year
Minute_second	Minutes and seconds, separated by ":"
Hour_second	Hours, minutes, and seconds, separated by ":"
Year_month	Year and month, separated by "-"

Below are three examples of the time interval:

①, increase the current time by 3 hours 20 minutes

Mysql> Select Current_timestamp; --Current time +---------------------+| Current_timestamp   |+---------------------+|  .- One- -  the: A: $ |+---------------------+1Rowinch Set(0.00sec) MySQL> SelectDate_add (Current_timestamp, Interval'3:20:00'Hour_second) asAdd_hoursecond;+---------------------+|Add_hoursecond|+---------------------+|  .- One- -  -: the: the |+---------------------+1Rowinch Set(0.00Sec

②, add date ' 2000-01-01 ' to 16 years 10 months

Mysql> SelectDate_add ('2000-01-01', Interval'16-10'Year_month) asAdd_year_month;+----------------+|Add_year_month|+----------------+|  .- One- on     |+----------------+1Rowinch Set(0.00Sec

　　

function last_day () : Returns the last day of the month you are seeking

Example: Seeking "2016-01-01" the last day of the month

Mysql> SelectLast_day ('2016-01-01');+------------------------+|Last_day ('2016-01-01')|+------------------------+|  .- on- to             |+------------------------+1Rowinch Set(0.00Sec

The Last_day () function returns a date value regardless of whether the supplied parameter is a date or a datetime type

2. Time function to return a string

　dayname (): returns the day of the week that is asked

Example: Ask the current date is the day of the week

Mysql> SelectDayname (current_date);+-----------------------+|Dayname (current_date)|+-----------------------+|Wednesday|+-----------------------+1Rowinch Set(0.00Sec

Year (), month (), day (), Time () ... Extracting part of Time

Mysql> Select  Year(current_date),Month(current_date), Day(current_date), Time (Current_timestamp);+--------------------+---------------------+-------------------+-------------------------+|  Year(current_date)| Month(current_date)|  Day(current_date)|TimeCurrent_timestamp)|+--------------------+---------------------+-------------------+-------------------------+|                . |                   One |                 - |  -: A: $                |+--------------------+---------------------+-------------------+-------------------------+1Rowinch Set(0.00Sec

3. Time function to return a number

DateDiff (): Calculates the number of days between two dates

Example: Calculate "2000-01-01" distance now how many days

Mysql> Select DateDiff(' 2016-11-30 ','2000-01-01');+--------------------------------------+| DateDiff(' 2016-11-30 ','2000-01-01')|+--------------------------------------+|                                 6178 |+--------------------------------------+1Rowinch Set(0.00Sec

Note: the DateDiff () function ignores the clock value in the parameter.

SQL Server also has the DateDiff () function, but can specify the type of time interval (such as year, month, day, hour, and so on). The way SQL Server implements the previous example:

Select DateDiff (Day '2016-11-30'2000-01-01');

MySQL Learning record-operating time data