MySQL Date and time functions

Time function

curdate () returns the current date Curtime () returns the current time now () returns the current date and time Unix_timestamp (date) Returns the date of the Unix timestamp example: SelectUnix_timestamp ('2017-1-1 01:23:23')SelectUnix_timestamp ('2017-1-1')SelectUnix_timestamp (now ()) Returns the result:1483205003from_unixtime (unixtime) input timestamp return date value with Unix_timestamp reciprocal operation example:SelectFrom_unixtime (1483205003WEEK (date) Returns the first few weeks of the year, for example:SelectWeek (now ()); Results 27YEAR (date) returns the year in which the given date is an example:Selectyear (now ()); result 2017HOUR (time) Returns an example of the hours given:SelectHOUR (Curtime ()); MINUTE (time) returns the minute examples of the given times:SelectMINUTE (Curetime ()); MONTHNAME (date) Returns an example of the English month name for date:SelectMONTHNAME (now ()); Results: July

Time formatting

Date_format (date, FMT) function; Format Date value by string FMT
Example:
Select Date_format (now (), '%M,%d,%Y ')  ; common format%y-%m-%d%h:%m:%s

%s,%s two digits in seconds (xx, on, ..., -)%i two digits in the form of points (xx, on, ..., -)%H two digits in the form of hours, -Hoursxx, on, ..., at)%h two digits in the form of hours, AHours on, Geneva, ..., A)%k digital form of the hour, -Hours0,1, ..., at)%l digital form of the hour, AHours1,2, ..., A)%T -hours of Time form (HH:MM:SS)%r Ahours of Time form (Hh:mm:ss AM or hh:mm:ss PM)%P AM or PM%W name of each day of the week (Sunday, Monday, ..., Saturday)%a abbreviation for the name of each day of the week (Sun, Mon, ..., Sat)%d two digits indicates the number of days in the month (xx, on,..., to)%e number represents the number of days in the month (1,2， ..., to)%D English suffix indicates the number of days in the month (1st, 2nd, 3rd,... )%w represents the number of days in a week in numbers (0= Sunday,1=monday, ...,6=Saturday)%j represents the number of days in a year in three digits (001,002, ...,366)%u weeks (0,1, the), where Sunday is the first day of the week%u weeks (0,1, the), where Monday is the first day of the week%M Month name (January, February, ..., December)%b Abbreviated month name (January, February,...., December)%m a two-digit month ( on, Geneva, ..., A)The month that is represented by the%c number (1,2, ...., A)%Y Four-bit number represents the year%y two-bit number represents the yearPercent direct value "%"

%Y Parameter type description

Adjust the time based on the original time

Datae_add (date,interval   expr   "-1_-2 "   as 　　　　　　　　　　　　　　　　　　After_oneyear_twomonth; # Note the quote ', or use a negative number

Date interval type for parameter type

microsecond    interval unit: Milliseconds second    interval unit: Seconds minute    interval unit: Minutes hour interval unit:    hour day interval unit:    Days week    interval unit: Week month    interval unit: Month quarter    interval unit: Quarter    Year interval unit: Second_microsecond    Compound, interval unit: seconds, milliseconds, expr can use two values to specify seconds and milliseconds respectively Minute_ Microsecond    compound type, interval unit: minute, millisecond minute_second    compound type, interval unit: minute, second Hour_microsecond    compound type, interval unit: hour, millisecond Hour_second    compound type, interval unit: hour, second Hour_minute    compound type, interval unit: Hour day_microsecond    compound type, interval unit: Days, milliseconds day_second    compound type, interval unit: Days, Second Day_minute    compound type, interval unit: Days, sub-day_hour    compound type, interval unit: day, hour year_month    compound type, interval unit: year, month

Days of difference between two days

DATEDIFF (Date1, Date2) calculates the number of days between two dates
Example:
Select DATEDIFF (' 2008-08-08 ', now ()); Results: 208

MySQL Date and time functions