Detailed explanation of MySql Date function
- 2021-10-27 09:31:20
- OfStack
The following are the most important built-in functions in MySQL
Function description
NOW () returns the current date and time
NOW() Returns the current date and time.
Grammar
NOW()
-- Instances
-- Below is SELECT Statement:
SELECT NOW(),CURDATE(),CURTIME()
The results are as follows:
NOW() CURDATE() CURTIME()
2018-06-27 14:25:34 2018-06-27 14:25:34
-- Instances
-- Below SQL Create a column with a date and time ( OrderDate ) "Orders" Table:
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)
-- Please note that, OrderDate Column provisions NOW() As the default value. As a result, when you insert rows into the table, the current date and time are automatically inserted into the columns.
-- Now, we want to "Orders" Insert into the table 1 Record:
INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')
-- "Orders" The table will look like this:
OrderId ProductName OrderDate
1 Jarlsberg Cheese 2018-06-11 14:36:55CURDATE () returns the current date
-- CURDATE() Returns the current date.
-- Instances
-- Below is SELECT Statement:
SELECT NOW(),CURDATE(),CURTIME()
The results are as follows:
NOW() CURDATE() CURTIME()
2018-06-27 14:25:34 2018-06-27 14:25:34
-- Instances
-- Below SQL Create a column with a date and time ( OrderDate ) "Orders" Table:
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT CURDATE(),
PRIMARY KEY (OrderId)
)
-- Please note that, OrderDate Column provisions CURDATE() As the default value. As a result, when you insert rows into the table, the current date and time are automatically inserted into the columns.
-- Now, we want to "Orders" Insert into the table 1 Record:
INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')
-- "Orders" The table will look like this:
OrderId ProductName OrderDate
1 Jarlsberg Cheese 2018-06-11 14:36:55CURTIME () returns the current time
-- CURTIME() Returns the current date.
-- Instances
-- Below is SELECT Statement:
SELECT NOW(),CURDATE(),CURTIME()
-- The results are as follows:
NOW() CURDATE() CURTIME()
2018-06-27 14:25:34 2018-06-27 14:25:34DATE () extracts the date part of a date or date/time expression
-- DATE() Function to extract a date or date / The date part of the time expression.
-- Instances
-- Suppose we have the following "Orders" Table:
OrderId ProductName OrderDate
1 Jarlsberg Cheese 2018-06-27 14:53:44.657
-- Below is SELECT Statement:
SELECT ProductName, DATE(OrderDate) AS OrderDate
FROM Orders
WHERE OrderId=1
-- The results are as follows:
ProductName OrderDate
Jarlsberg Cheese 2018-06-27EXTRACT () returns a separate part of the date/time
-- EXTRACT() The function is used to return a date / Separate parts of time, such as year, month, day, hour, minute, etc.
-- Grammar
-- EXTRACT(unit FROM date)
-- date Parameter is a valid date expression. unit Parameters can be the following values:
Unit Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
-- Instances
-- Suppose we have the following "Orders" Table:
OrderId ProductName OrderDate
1 Jarlsberg Cheese 2018-06-2715:03:01
-- Below is SELECT Statement:
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1
-- The results are as follows:
OrderYear OrderMonth OrderDay
2018 06 27DATE_ADD () adds the specified time interval to the date
-- DATE_ADD() Function to add the specified time interval to the date.
-- Grammar
-- DATE_ADD(date,INTERVAL expr type)
-- date Parameter is a valid date expression. expr The parameter is the time interval you want to add.
-- type Parameters can be the following values:
Type Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
-- Instances
-- Suppose we have the following "Orders" Table:
OrderId ProductName OrderDate
1 Jarlsberg Cheese 2018-06-27 15:02:47
-- Now, we want to tell "OrderDate" Add 45 Days, so that you can find the payment date.
-- We use the following SELECT Statement:
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
FROM Orders
-- Results:
OrderId OrderPayDate
1 2018-08-11 15:02:47DATE_SUB () subtracts the specified time interval from the date
DATE_SUB() Function subtracts the specified time interval from the date.
Grammar
DATE_SUB(date,INTERVAL expr type)
date Parameter is a valid date expression. expr The parameter is the time interval you want to add.
type Parameters can be the following values:
Type Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
Instances
Suppose we have the following "Orders" Table:
OrderId ProductName OrderDate
Jarlsberg Cheese 2018-06-27 15:02:47
Now, we want to tell "OrderDate" Subtract 5 God.
We use the following SELECT Statement:
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate
FROM Orders
Results:
OrderId SubtractDate
2018-06-22 15:02:47DATEDIFF () returns the number of days between two dates
DATEDUFF() Returns the number of days between two dates
Example:
SELECT DATEDIFF('2018-11-29','2018-11-30') AS DiffDate
Results:
DiffDate
-1DATE_FORMAT () displays date/time in different formats
DATE_FORMAT() Display dates in different formats / Time
Example:
SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
# DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
'Jun 27 2018 03:12 PM'
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y');
# DATE_FORMAT(NOW(),'%m-%d-%Y')
'06-27-2018'
SELECT DATE_FORMAT(NOW(),'%d %b %y');
# DATE_FORMAT(NOW(),'%d %b %y')
'27 Jun 18'
SELECT DATE_FORMAT(NOW(),'%d %b %Y %T:%f');
# DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
'27 Jun 2018 15:15:44:000000'
The formats you can use are:
Format Describe
%a Abbreviated week name
%b Abbreviated month name
%c Month, value
%D Day of the month with English prefix
%d Day of the month, value ( 00-31 )
%e Day of the month, value ( 0-31 )
%f Microsecond
%H Hours ( 00-23 )
%h Hours ( 01-12 )
%I Hours ( 01-12 )
%i Minutes, numeric value ( 00-59 )
%j The days of the year ( 001-366 )
%k Hours ( 0-23 )
%l Hours ( 1-12 )
%M Month name
%m Month, value ( 00-12 )
%p AM Or PM
%r Time, 12- Hours ( hh:mm:ss AM Or PM )
%S Seconds ( 00-59 )
%s Seconds ( 00-59 )
%T Time , 24- Hours ( hh:mm:ss )
%U Week ( 00-53 ) Sunday is 1 The first of the week 1 Days
%u Week ( 00-53 ) Week 1 Yes 1 The first of the week 1 Days
%V Week ( 01-53 ) Sunday is 1 The first of the week 1 Heaven, and %X Use
%v Week ( 01-53 ) Week 1 Yes 1 The first of the week 1 Heaven, and %x Use
%W Name of the week
%w Day of the week ( 0= Sunday , 6= Week 6 )
%X In which Sunday is the first day of the week 1 God, 4 Bit, and %V Use
%x Year, one of the weeks 1 Is the first of the week 1 God, 4 Bit, and %v Use
%Y In, 4 Bit
%y In, 2 Bit The following are the data types for storing dates in the database
DATE-Format: YYYY-MM-DD
DATETIME-Format: YYYY-MM-DD HH: MM: SS
TIMESTAMP-Format: YYYY-MM-DD HH: MM: SS
YEAR-Format: YYYY or YY
Summary: The date function is a bit messy, in fact, I think it is OK to remember a few often used, not 1 must remember all, who must not have the ability to never forget anything, and will not give a test, which one can't remember to check in the work