Mysql date and time processing functions

Mysql date and time processing functions

• ADDDATE (date, interval expr unit), ADDDATE (expr, days) --- add the time value (Time Zone) as the date value, which is the alias of DATE_ADD ()

DATA_ADD (date, INTERVAL expr unit), DATE_SUB (date, INTERVAL expr unit)

These functions perform time operations. The date parameter specifies the date or date and Time Value of the start date. expr is the start date of an expression that specifies the interval between the values to be added or subtracted.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

 

You can also write

date + INTERVAL expr unitdate - INTERVAL expr unit

 

mysql> select '2008-1-31' + interval 1 month;+--------------------------------+| '2008-1-31' + interval 1 month |+--------------------------------+| 2008-02-29                     |+--------------------------------+1 row in set (0.00 sec)mysql> select date_add('2008-1-31',interval 1 month);+----------------------------------------+| date_add('2008-1-31',interval 1 month) |+----------------------------------------+| 2008-02-29                             |+----------------------------------------+1 row in set (0.00 sec)

 

Obtain the start and end time of a day.

mysql> select '2015-4-4 0:0:0' + interval 1 day - interval 1 second;+-------------------------------------------------------+| '2015-4-4 0:0:0' + interval 1 day - interval 1 second |+-------------------------------------------------------+| 2015-04-04 23:59:59                                   |+-------------------------------------------------------+1 row in set (0.00 sec)

 

 

• ADDTIME (expr1, expr2) --- add expr2 to expr1 and return the result; expr1 is a time or date and time expression, and expr2 is a time expression

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');        -> '2008-01-02 01:01:01.000001'mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');        -> '03:00:01.999997'

• CONVERT_TZ () Convert from one timezone to another
• CURDATE () --- returns the current date YYYY-MM-DD,

mysql> select curdate();+------------+| curdate()  |+------------+| 2015-04-03 |+------------+1 row in set (0.00 sec)mysql> select curdate() + 0;+---------------+| curdate() + 0 |+---------------+|      20150403 |+---------------+1 row in set (0.00 sec)

 

• CURRENT_DATE (), CURRENT_DATE ---- alias of CURDATE ()
• CURRENT_TIME (), CURRENT_TIME --- alias of CURTIME ()
• CURRENT_TIMESTAMP (), the alias of CURRENT_TIMESTAMP --- NOW ()
• CURTIME () --- returns the current time

mysql> select curtime();+-----------+| curtime() |+-----------+| 14:06:30  |+-----------+1 row in set (0.00 sec)mysql> select curtime() + 0;+---------------+| curtime() + 0 |+---------------+| 140641.000000 |+---------------+1 row in set (0.00 sec)

 

• DATE_ADD () Add time values (intervals) to a date value
• DATE_FORMAT (date, format) format the date based on the formatted string

The '%' specifier can be used in a format string.

Common:

% A -- abbreviation of the day of the week (Sun .. Sat)

% B -- abbreviated month (Jan... Dec)

% C -- month (0--12)

% D -- the number of days is suffixed with an English suffix (0th, 1sh ...)

% D, % e -- days in the month (0-31)

% H -- 24-hour system (00-23)

% H, % I -- 12 hour (00-12)

% I -- minute (00-59)

% M -- month (January, December)

% M -- month (00-12)

% S, % s -- seconds (00-59)

% Y -- four digits per year

% Y -- two places in a year

mysql> select date_format(now(),'%Y/%m/%d %H:%m:%s');+----------------------------------------+| date_format(now(),'%Y/%m/%d %H:%m:%s') |+----------------------------------------+| 2015/04/03 14:04:05                    |+----------------------------------------+1 row in set (0.00 sec)

 

 

• DATE_SUB () Subtract a time value (interval) from a date
• DATE () -- extract DATE

mysql> select date(now());+-----------------+| date(curdate()) |+-----------------+| 2015-04-03      |+-----------------+1 row in set (0.00 sec)

 

• DATEDIFF (expr1, expr2) returns the time difference between expr1-expr2; expr1, expr2 expressions only involve the date part in the operation

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');+----------------------------------------------+| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |+----------------------------------------------+|                                            1 |+----------------------------------------------+1 row in set (0.00 sec)

 

• DAY () Synonym for DAYOFMONTH ()
• DAYNAME () Return the name of the weekday
• DAYOFMONTH () Return the day of the month (0-31)
• DAYOFWEEK () Return the weekday index of the argument
• DAYOFYEAR () Return the day of the year (1-366)
• EXTRACT () Extract part of a date
• FROM_DAYS () Convert a day number to a date
• FROM_UNIXTIME (unix_timestamp [, format]) format unix timestamps as date strings

mysql> select from_unixtime(unix_timestamp(),'%Y-%m-%d');+--------------------------------------------+| from_unixtime(unix_timestamp(),'%Y-%m-%d') |+--------------------------------------------+| 2015-04-03                                 |+--------------------------------------------+1 row in set (0.00 sec)

 

• GET_FORMAT () Return a date format string
• HOUR (time) -- extract HOUR Part

mysql> select HOUR(now());+-------------+| HOUR(now()) |+-------------+|          14 |+-------------+1 row in set (0.00 sec)

 

• LAST_DAY (date) -- returns the last day of the specified month. If the parameter is invalid, NULL is returned.

mysql> select last_day('2013-2-5');+----------------------+| last_day('2013-2-5') |+----------------------+| 2013-02-28           |+----------------------+1 row in set (0.00 sec)mysql> select last_day('2004-2-5');+----------------------+| last_day('2004-2-5') |+----------------------+| 2004-02-29           |+----------------------+1 row in set (0.00 sec)mysql> select last_day('2004-2-35');+-----------------------+| last_day('2004-2-35') |+-----------------------+| NULL                  |+-----------------------+1 row in set, 1 warning (0.00 sec)

 

• LOCALTIME (), LOCALTIME Synonym for NOW ()
• LOCALTIMESTAMP, LOCALTIMESTAMP () Synonym for NOW ()
• MAKEDATE (year, dayofyear) Create a date from the year and day of year
• MAKETIME (hour, minute, second)
• MICROSECOND (expr) -- returns the number of milliseconds of the expr expression

• MINUTE () Return the minute from the argument
• MONTH () Return the month from the date passed
• MONTHNAME (date) --- returns the full name of a month.

mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| April            |+------------------+1 row in set (0.00 sec)

 

• NOW () -- returns the current date and time
• PERIOD_ADD () Add a period to a year-month
• PERIOD_DIFF () Return the number of months between periods
• QUARTER () Return the quarter from a date argument
• SEC_TO_TIME () Converts seconds to 'hh: MM: ss' format
• SECOND () -- returns the number of seconds (0-59)
• STR_TO_DATE () Convert a string to a date
• SUBDATE () A synonym for DATE_SUB () when invoked with three arguments
• SUBTIME () Subtract times
• SYSDATE () Return the time at which the function executes
• TIME_FORMAT () Format as time
• TIME_TO_SEC () Return the argument converted to seconds
• TIME () Extract the time portion of the expression passed
• TIMEDIFF () Subtract time
• TIMESTAMP () With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
• TIMESTAMPADD () Add an interval to a datetime expression
• TIMESTAMPDIFF () Subtract an interval from a datetime expression
• TO_DAYS () Return the date argument converted to days
• TO_SECONDS () Return the date or datetime argument converted to seconds since Year 0
• UNIX_TIMESTAMP () -- returns the unix Timestamp

mysql> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+|       1428042298 |+------------------+1 row in set (0.00 sec)

 

• UTC_DATE () Return the current UTC date
• UTC_TIME () Return the current UTC time
• UTC_TIMESTAMP () Return the current UTC date and time
• WEEK () Return the week number
• WEEKDAY () Return the weekday index
• WEEKOFYEAR () Return the calendar week of the date (0-53)
• YEAR () --- get YEAR

mysql> select year(now());+-------------+| year(now()) |+-------------+|        2015 |+-------------+1 row in set (0.00 sec)

 

• YEARWEEK () Return the year and week