mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
Note that the word INTERVAL and type keywords are not case sensitive from the previous example.
TO_DAYS(date)
Given a date, returning a number of days (from 0 years).
mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669
TO_DAYS()
Not intended to be used to use the value before the occurrence of Gregorian calendar (1582).
FROM_DAYS(N)
Given a number of days N, returning a DATE value.
mysql> select FROM_DAYS(729669);
-> '1997-10-07'
DATE_FORMAT(date,format)
Format date value according to format string. The following modifiers can be used in format strings: %M Monthly name (January...December)
%W The name of the week (Sunday…Saturday)
%D Date of the month with English prefixes (1st, 2nd, 3rd, etc.)
%Y year, digit, 4 digit
%y year, number, 2 digits
%a Abbreviated name of the week (Sun…Sat)
%d The number of days in the month, the number (00…31)
%e The number of days in the month, the number (0…31)
%m month, number(01…12)
%c month, number (1…12)
%b abbreviated month name (Jan...Dec)
%j The number of days in a year (001…366)
%H Hours (00…23)
%k hours (0…23)
%h hours (01…12)
%I Hours(01…12)
%l hours (1…12)
%i minutes, 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 The number of days in a week (0=Sunday …6=Saturday )
%U Week (0…52), Sunday here is the first day of the week
%u Week (0…52), Monday here is the first day of the week
%% A text “%”.
All other characters are copied into the result without explanation.
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
In MySQL 3.23, % is required before the format modifier character. In earlier versions of MySQL, % is optional.
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function above, but the format string can only contain those format modifiers that handle hours, minutes, and seconds. Other modifiers produce a NULL value or 0.
CURDATE()
CURRENT_DATE
Returns the 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();
-> '1997-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 used in a string or in the context of a number.
mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If no parameter is called, a Unix timestamp is returned (seconds starting from '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a date parameter, it returns the second value starting from '1970-01-01 00:00:00' GMT. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number of local time in YYMMDD or YYYYMMDD format.
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-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.
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 numeric context.
mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
Returns a string representing the Unix time stamp, 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');
-> '1997 23rd December 03:43:30 x'
SEC_TO_TIME(seconds)
Returns the seconds parameter, transformed into hours, minutes, and seconds, and the value is formatted in 'HH:MM:SS' or HHMMSS, depending on whether the function is used in a string or in a numeric 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 and converts it to seconds.
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
Previous page12Read the full text