mysql Common time related operations

Return current date :CURRENT_DATE()

select CURRENT_DATE() from  date_test 

Return current time :CURRENT_TIME()

select CURRENT_TIME() from  date_test 

Returns the current date and time :NOW()

select NOW() from  date_test 

Returns the current date and time :CURRENT_TIMESTAMP()

select CURRENT_TIMESTAMP() from  date_test  

Timestamp conversion ：UNIX_TIMESTAMP($date)

$date： Specified date time

select UNIX_TIMESTAMP(now()) from  date_test  # Convert the current date to a timestamp 

Timestamp formatting :FROM_UNIXTIME($uni{x}_{t}imestamp,$format_type)

$unix_timestamp The specified time stamp
$format_type Can be used in format_type in ：
%M The name of the month (January……December)
%W The name of the week (Sunday……Saturday)
%D The date of the month with the English prefix (1st, 2nd, 3rd, wait .）
%Y year , Numbers , 4 position
%y year , Numbers , 2 position
%a Abbreviated week name (Sun……Sat)
%d Days in a month , Numbers (00……31)
%e Days in a month , Numbers (0……31)
%m month , Numbers (01……12)
%c month , Numbers (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 minute , Numbers (00……59)
%r Time ,12 Hours (hh:mm:ss [AP]M)
%T Time ,24 Hours (hh:mm:ss)
%S second (00……59)
%s second (00……59)
%p AM or PM
%w Days of the week (0=Sunday ……6=Saturday ）
%U week (0……52), Here Sunday is the first day of the week
%u week (0……52), Monday is the first day of the week

select FROM_UNIXTIME(1657791851,'%Y-%c-%d %h:%i:%s') from date_test  # Returns the current date   

Return year : YEAR($date)

$date： Specified date time

select YEAR(now()) from  date_test  # Returns the current year 

Return to the month ：MONTH($date)

$date： Specified date time

select MONTH(now()) from  date_test  # Returns the current month 

Return the number :DAY($date)

$date： Specified date time

select DAY(now()) from  date_test  # Returns the current day 

return ：HOUR($date)

$date： Specified date time

select HOUR(now()) from  date_test  # Returns the current time 

Return points ：MINUTE($date)

$date： Specified date time

select MINUTE(now()) from  date_test  # Returns the current score 

Return seconds ：SECOND($date)

$date： Specified date time

select SECOND(now()) from  date_test  # Returns the current second 

Format time ： DATE_FORMAT($date,$format_type)

ps: Convert time to specified format
$date： Specified date time
$format_type Can be used in format_type in ：
%M The name of the month (January……December)
%W The name of the week (Sunday……Saturday)
%D The date of the month with the English prefix (1st, 2nd, 3rd, wait .）
%Y year , Numbers , 4 position
%y year , Numbers , 2 position
%a Abbreviated week name (Sun……Sat)
%d Days in a month , Numbers (00……31)
%e Days in a month , Numbers (0……31)
%m month , Numbers (01……12)
%c month , Numbers (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 minute , Numbers (00……59)
%r Time ,12 Hours (hh:mm:ss [AP]M)
%T Time ,24 Hours (hh:mm:ss)
%S second (00……59)
%s second (00……59)
%p AM or PM
%w Days of the week (0=Sunday ……6=Saturday ）
%U week (0……52), Here Sunday is the first day of the week
%u week (0……52), Monday is the first day of the week

select DATE_FORMAT('2022-07-06 10:04:35','%Y-%m-%d') from  date_test  # Returns the current second 

Time increases ：DATE_ADD($date,interval $index $date_type)

ps： The returned date and time
$date: For the specified date
$index: Specific values , For example, increase 1 God , Then for 1
$date_type ： Time type , The value is
day: God
hour: when
minute: branch
second: second
week: Zhou
month: month
quarter: quarter
year: year

select DATE_ADD('2022-07-05 00:59:59', interval 2 day) as result from  date_test  # Two more days 

Time reduction ：DATE_SUB($date,interval $index $date_type)

ps： The returned date and time
$date: For the specified date
$index: Specific values , For example, reduce 1 God , Then for 1
$date_type ： Time type , The value is
day: God
hour: when
minute: branch
second: second
week: Zhou
month: month
quarter: quarter
year: year

select DATE_SUB('2022-07-05 00:59:59', interval 2 day) as result from  date_test  # Two days less 

Time difference ：DATEDIFF($en{d}_{d}ate,$begin_date)

ps： Returns the subtraction of two dates and times , Get the difference in days ,
Be careful ： Time （ Minutes and seconds ） Will not participate in the calculation
$end_date: End time
$begin_date: Start time

select DATEDIFF('2022-07-09 00:59:59', '2022-07-07 12:59:59') as result from  date_test  # Subtract two times to get days 

Time difference ：TIMESTAMPDIFF($dat{e}_{t}ype,$begin_date, $end_date)

ps： Returns the subtraction of two dates and times , Get the time value of the specified type
Be careful ： Time （ Minutes and seconds ） Will participate in the calculation
$end_date: End time
$begin_date: Start time
$date_type: The value is
day: God
hour: when
minute: branch
second: second
week: Zhou
month: month
quarter: quarter
year: year

select TIMESTAMPDIFF(day, '2022-07-07 12:59:59','2022-07-09 00:59:59') as result from  date_test 

Time difference ：TIMEDIFF($en{d}_{d}ate,$begin_date)

ps： Returns the subtraction of two dates and times , Get a time difference
Be careful ： Time （ Minutes and seconds ） Will participate in the calculation
$end_date: End time
$begin_date: Start time

select TIMEDIFF('2022-07-09 00:59:59', '2022-07-07 12:59:59') as result from  date_test   # Subtract two times to get time 

^ ^
 -