Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Date refers to year, month and day, and time refers to hour, minute and second

1 Obtain the data of the current moment

1.1 Get the date and time of the current moment

SELECT NOW()
Copy the code

result:

1.2 Obtaining the date information of the current time

1.2.1 Obtaining the date

(1) Obtain the value from curdate()

SELECT CURDATE()
Copy the code

result:

(2) Get by now()

SELECT DATE(NOW())
Copy the code

result:

1.2.2 Get the year in date

SELECT YEAR(NOW())
Copy the code

result:

1.2.3 Obtaining the month in the date

SELECT MONTH(NOW())
Copy the code

result:

1.2.4 Obtaining the day in the date

SELECT DAY(NOW())
Copy the code

result:

1.3 Obtaining the current time Information

1.3.1 Obtaining the current time

(1) Obtain from curtime()

SELECT CURTIME()
Copy the code

result:

(2) Get by now()

SELECT TIME(NOW())
Copy the code

result:

1.3.2 When obtaining the current moment

SELECT HOUR(NOW())
Copy the code

result:

1.3.3 Get the score of the current moment

SELECT MINUTE(NOW())
Copy the code

result:

1.3.4 Obtaining the seconds of the current moment

SELECT SECOND(NOW())
Copy the code

result:

1.4 Obtaining the weekly information of the current moment

1.4.1 Which week of the year is the current time

SELECT WEEKOFYEAR(NOW())
Copy the code

result:

1.4.2 Checking the current day of the week

SELECT DAYOFWEEK(NOW())
Copy the code

result:

1.5 Obtain quarterly information at the current time

SELECT 
    QUARTER ( "2019-01-01" ) AS quarter_1,
    QUARTER ( "2019-04-01" ) AS quarter_2,
    QUARTER ( "2019-07-01" ) AS quarter_3,
    QUARTER ( "2019-10-01" ) AS quarter_4
Copy the code

result:

Date and time format conversion

2.1 Format Conversion

The format conversion uses the date_format() function, which is used as follows:

date_format(datetime, format)

Datetime indicates the date and time to be converted, and format indicates the format to be converted

SELECT DATE_FORMAT("2019-12-25", "%Y-%m-%d")
Copy the code

result:

SELECT DATE_FORMAT("2019-1-25", "%Y-%m-%d")
Copy the code

result:

Notice the difference between 1 and 01 here. The original date is 2019-1-25, and the return result is 2019-01-25

SELECT DATE_FORMAT("2019-1-25 12:30:45", "%H:%i:%S")
Copy the code

result:

2.2 Date Extraction

Here we use extract() ambiguity of the form:

extract(unit from datetime)

Datetime indicates the specific date and time, and unit indicates the part to be extracted

Unit values are as follows:

unit	instructions
year	years
month	month
day	day
hour	hours
minute	minutes
second	seconds
week	Number of weeks, week of the year

SELECT  EXTRACT(year FROM "2019-12-23")
Copy the code

result:

Date and time operations

3.1 Backward Offset date and time

The backoffset uses the date_add() function, which looks like this:

date_add(date, interval num unit)

Date indicates the date and time, interval is a fixed parameter, num is the offset, and unit is the offset unit

SELECT
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 YEAR) AS yaer,
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 MONTH) AS month,
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 DAY) AS date,
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 HOUR) AS hour,
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 MINUTE) AS minute,
DATE_ADD("2021-01-01 12:34:56",INTERVAL 7 SECOND) AS second
Copy the code

result:

3.2 Forward offset date and time

The backoffset uses the date_sub() function, which looks like this:

date_sub(date, interval num unit)

Date indicates the date and time, interval is a fixed parameter, num is the offset, and unit is the offset unit

SELECT
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 YEAR) AS yaer,
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 MONTH) AS month,
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 DAY) AS date,
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 HOUR) AS hour,
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 MINUTE) AS minute,
DATE_SUB("2021-01-01 12:34:56",INTERVAL 7 SECOND) AS second
Copy the code

result:

3.3 Take the difference between two dates

SELECT DATEDIFF("2019-01-07","2019-01-01")
Copy the code

result:

3.4 Comparison between the two dates

SELECT
"2019-01-01" > "2019-01-02" as co11,
"2019-01-01" < "2019-01-02" as co12,
"2019-01-01" = "2019-01-02" as co13,
"2019-01-01" != "2019-01-02" as co14
Copy the code

result: