编程知识 cdmana.com

MySQL date and time function summary

This article is based on MySQL8.0

In this paper, MySQL Functions about date and time operations .


Date and time functions
function describe
ADDDATE() Add a time value to a date value
ADDTIME() add to time
CONVERT_TZ() From one time zone to another
CURDATE() Return current date
CURRENT_DATE(), CURRENT_DATE and CURDATE() Synonymous
CURRENT_TIME(), CURRENT_TIME and CURDATE() Synonymous
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP and NOW() Synonymous
CURTIME() Return current time
DATE() Gets the date part of the date or datetime expression
DATE_ADD() Time value ( interval ) Add to date value
DATE_FORMAT() Format the date in the specified format
DATE_SUB() Subtract the time value from the date ( interval )
DATEDIFF() Subtract two dates
DAY() and DAYOFMONTH() Synonymous
DAYNAME() Returns the name of the working day
DAYOFMONTH() Returns the date of the month (0-31)
DAYOFWEEK() Returns the sort of weekdays according to the parameters
DAYOFYEAR() One day of the year (1-366)
EXTRACT() Get a part of the date
FROM_DAYS() Convert the serial number of a day to a date
FROM_UNIXTIME() take Unix The timestamp is formatted as a date
GET_FORMAT() Return date format string
HOUR() For hours
LAST_DAY Returns the last day of the parameter month
LOCALTIME(), LOCALTIME and NOW() Synonymous
LOCALTIMESTAMP, LOCALTIMESTAMP() and NOW() Synonymous
MAKEDATE() From year and day creation date
MAKETIME() Since then 、 branch 、 Second creation time
MICROSECOND() Return microseconds from parameters
MINUTE() Return the parameter minutes
MONTH() Returns the month from which the date has passed
MONTHNAME() Returns the name of the month
NOW() Returns the current date and time
PERIOD_ADD() Give new year - Month add a period of time
PERIOD_DIFF() Returns the number of months between periods of time
QUARTER() Returns the quarter from the date parameter
SEC_TO_TIME() Convert seconds to “hh:mm:ss” Format
SECOND() Return seconds (0-59)
STR_TO_DATE() Convert string to date
SUBDATE() When called with three parameters , and DATE_SUB() Synonymous
SUBTIME() Subtracted Time
SYSDATE() Returns the time the function was executed
TIME() Get the time according to the passed expression
TIME_FORMAT() The format is time
TIME_TO_SEC() Returns the parameter converted to seconds
TIMEDIFF() Time minus
TIMESTAMP() For a single parameter , This function returns the date or date time expression ; For two parameters , Returns the sum of the parameters
TIMESTAMPADD() Add an interval to the datetime expression
TIMESTAMPDIFF() Subtract the interval from the date time expression
TO_DAYS() Returns the date parameter converted to day
TO_SECONDS() Return from 0 Date or date time parameter converted to seconds from year
UNIX_TIMESTAMP() return Unix Time stamp
UTC_DATE() Returns the current UTC date
UTC_TIME() Returns the current UTC Time
UTC_TIMESTAMP() Returns the current UTC Date and time
WEEK() Back to the number of weeks
WEEKDAY() Return the work day serial number
WEEKOFYEAR() Return date (1-53) Calendar week of
YEAR() Year of return
YEARWEEK() Return year and week

Take a look at MySQL Examples of common date and time functions .


Get the current date time

  • CURDATE()
    With “YYYY-MM-DD” or YYYYMMDD Format returns the current date , It depends on whether the function is used in a string context or in a numeric context .
mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613
  • CURTIME([fsp])
    With 'hh:mm:ss' or hhmmss Format returns the current time , It depends on whether the function is used in a string context or in a numeric context . The value is expressed in the session time zone .

If specified fsp The precision is from 0 To 6 Number of decimal places , Then specify from 0 To 6 The decimal precision of .

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000
  • NOW([fsp])
    With 'YYYY-MM-DD hh:MM:ss' or YYYYMMDDhhmmss Format returns the current date and time , It depends on whether the function is used in a string context or in a numeric context . The value is expressed in the session time zone .
mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

Time and date format conversion

The same date and time can be expressed in many different ways , Sometimes you need to switch between different formats . stay MySQL What we use date_format() function :

  • DATE_FORMAT(date,format): Format the date value according to the format string .

The relevant format is as follows , Except for this function , The format can also be used for :STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

Format describe
%a Abbreviated week name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c month , The number (0..12)
%D Days of the month with English prefix (0th, 1st, 2nd, 3rd, …)
%d One day in a month , The number (00..31)
%e One day in a month , The number (0..31)
%f Microsecond (000000..999999)
%H Hours (00..23)
%h Hours (01..12)
%I Hours (01..12)
%i minute , The number (00..59)
%j One day of the year (001..366)
%k Hours (0..23)
%l Hours (1..12)
%M Month name (January..December)
%m month , The number (00..12)
%p AM or PM
%r Time , 12 - Hours (hh:mm:ss AM perhaps PM)
%S second (00..59)
%s second (00..59)
%T Time , 24 - Hours (hh:mm:ss)
%W Week name (Sunday..Saturday)
%w What day of the week (0=Sunday..6=Saturday)
%Y year , The number , 4 A digital
%y year , The number , 2 A digital
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 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'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

Date time operation

  • DATE_ADD(date,INTERVAL expr unit),
  • DATE_SUB(date,INTERVAL expr unit)
    These functions perform date operations , To date forward / A backward shift .date Parameter specifies the start date or date time value .expr It's an expression , Specifies the interval value to add or subtract from the start date .expr Is evaluated as a string ; It can take - The beginning indicates a negative interval .unit It's a keyword , Indicates the unit the expression should use .
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'
  • DATEDIFF(expr1,expr2)
    DATEDIFF() return expr1−expr2, In days from one date to another .expr1 and expr2 It's a date or a date and time expression . Only the date part of the value is used in the calculation .
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31




Reference resources :

【1】:12.7 Date and Time Functions
【2】: MySQL Get the current date time function
【3】: One article Mysql Date time function

版权声明
本文为[Three evils]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201209232252459i.html

Scroll to Top