Fully collected Mysql date and mysql Time Functions
DATE_FORMAT (date, format)
Format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (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 a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".
All other characters are not interpreted and copied to the result.
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
'% D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
'% H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'
In MySQL3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.
TIME_FORMAT (time, format)
This is used like the preceding DATE_FORMAT () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds.
Other modifiers generate a NULL value or 0.
CURDATE ()
CURRENT_DATE
Returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
Mysql> select CURDATE ();
-> '2017-12-15'
Mysql> select CURDATE () + 0;
-> 19971215
CURTIME ()
CURRENT_TIME
Returns the current time value in 'hh: MM: ss' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select CURTIME () + 0;
-> 235026
NOW ()
SYSDATE ()
CURRENT_TIMESTAMP
Returns the current date and time in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on whether the function is in a string or in a number
The context is used.
Mysql> select NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () + 0;
-> 19971215235026
UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a DATE string, a datetime string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format.
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function will directly accept the value, without the implicit "string-to-unix-timestamp" transformation http://www.knowsky.com /.
FROM_UNIXTIME (unix_timestamp)
Returns the value represented by the unix_timestamp parameter in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or number context.
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (),
'% Y % D % M % h: % I: % s % x ');
-> '2014 23rd December 03:43:30 x'
SEC_TO_TIME (seconds)
Returns the seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: ss' or HHMMSS, depending on whether the function is used in a string or a number context.
Mysql> select SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select SEC_TO_TIME (2378) + 0;
-> 3938
TIME_TO_SEC (time)
Returns the time parameter, which is converted to seconds.
Mysql> select TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> select TIME_TO_SEC ('00: 39: 38 ');
-> 2378