MySQL date-related functions

1. Get the current time:

/** Get the current date + time (date + times) function: Now (), Common **/SelectNow () fromdual;/** Get current timestamp, current_timestamp or Current_timestamp () **/Select Current_timestamp,Current_timestamp() fromdual;/** Get the current date + time (date + times) function: Sysdate (), unlike now (), the Now () is obtained at the execution start value, and sysdate () Gets the value dynamically when the function executes **/SelectNow (), Sleep (3), Sysdate () fromDual

Operation Result:

2. Date/Time operation

If you have an index on a date column, it is not recommended that you perform a date function operation on these fields, because the index of the column will not be valid. Instead of columns, you should change your mind and perform function operations on the values of the operations.

The format string where the date is formatted is:

Specifiers	Description
%a	Abbreviated name of the weekday (Sun.. Sat)
%b	Abbreviated name of the month (Jan: DEC)
%c	month, digital form (0..12)
%d	Date of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Date of the month, digital form (00..31)
%e	Date of the month, digital form (0..31)
%f	microseconds (000000..999999)
%H	Hours (00..23)
%h	Hours (01..12)
%I	Hours (01..12)
%i	Minutes, digital form (00..59)
%j	Number of days in the year (001..366)
%k	Hours (0..23)
%l	Hours (1..12)
%M	Month name (January): December)
%m	month, digital form (00..12)
%p	Morning (AM) or PM (PM)
%r	Time, 12 hours (hours hh: minutes mm: seconds SS after plus am or PM)
%s	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24 hours (hours hh: minutes mm: Seconds ss)
%u	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), of which Sunday is the first day of the week; Simultaneous use with%x
%v	Week (01..53), of which Monday is the first day of the week; Simultaneous use with%x
%W	Weekday Name (Sunday: Saturday
%w	Daily of the Week (0= Sunday). 6= Saturday)
%x	The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%x	The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%Y	Year, number form, 4 digits
%y	Year, digital form (2 digits)
%%	'% ' literal characters

2.1. Date/Time to string, string to date/time

Common:

1 /**2 The date is formatted into a string, MySQL is more lenient on date processing, Data_format's first parameter is a date format string can actually parse, but it is not recommended to do so3 **/4 SelectDate_format (now (),'%y%m%d%h:%i:%s');5 SelectDate_format ('20160711111203','%y-%m-%d%h:%i:%s');6 7 /**8 converts a date format string to a date/time that supports date9 **/Ten SelectStr_to_date ('2016-07-11 11:12:03','%y-%m-%d%h:%i:%s'); One  A /** - convert time (not including date) to string form - **/ the SelectTime_format (now (),'%y%m%d%h:%i:%s');

Operation Result:

20160712 One:£ º

 . -  - -  One One::

 . -  - -  One One::

00000000 One:£ º

Other:

(date, day) conversion function: To_days (date), From_days (days)

/* * Result: 736522* */ Select  from dual; /* * Result: 1916-07-15* */ Select From_days (736522 from dual;

(Time, seconds) conversion function: Time_to_sec (times), sec_to_time (seconds)

/* * Result: 3605* */ Select time_to_sec ('01:00:05'/* * Results: 01:00:05* */ Select sec_to_time (3605

Patchwork date, Time function: Makdedate (year,dayofyear), Maketime (Hour,minute,second)

/** Result: 2016-01-30**/SelectMakedate ( ., -);/** Result: 2016-02-01**/SelectMakedate ( ., +); /** Result: 12:15:30**/SelectMaketime ( A, the, -);

(Unix timestamp, date) conversion function: Unix_timestamp (), Unix_timestamp (date), From_unixtime (Unix_timestamp), From_unixtime (unix_ Timestamp,format)

SelectNow (), Unix_timestamp ();--2016-07-12 11:41:56, 1468294916SelectUnix_timestamp ('2016-07-12');--1468252800SelectUnix_timestamp ('2016-07-12 11:38:37');--1468294717SelectFrom_unixtime (1468294702);--2016-07-12 11:38:22SelectFrom_unixtime (1468294717);--2016-07-12 11:38:37SelectFrom_unixtime (1468294717,'%Y%d%M%h:%i:%s%x');--12th July 11:38:37

2.2. Date Calculation function

111

MySQL date-related functions