An article to deal with Mysql date and time functions

Preface

The date and time functions are also frequently used in our daily work. In this article, we will mainly talk about the date and time related functions in MySQL. They are basically the same between different databases, with only individual functions being different. Everyone has a database, and if you encounter something you don’t know, you can just look it up.

1. Get the current time

1.1 Return the current date and time

1.2 Get the current date

1.3 Get the current time

1.4 Get the current week number

2. Date and time format conversion

3. Date and time operations

3.1 Backward shift time

3.2 Forward shift time

3.3 Difference between two dates

The article is quite long, so it is recommended that you save it and read it slowly.

1. Get the current time

Getting the current time means getting the time-related data at the moment the program is running, such as year, month, day, hour, minute, second, and other information.

1.1 Return the current date and time

To return the current date and time, the now() function is used in ESql. Simply write the now() function after the select. The specific code is as follows:

select now()

Running the above code will get the year, month, day, hour, minute and second information of the moment your program is running, for example: 2019-12-25 22:47:37.

1.2 Get the current date

The previous now() function obtains the current date and time. Sometimes we may only need the current date part and do not need the time part. At this time, replace the now() function with the curdate() function in Sql to obtain the current date part. The specific code is as follows:

select curdate()

By running the above code, we get the date part of the current moment: 2019-12-25

The curdate() function directly obtains the date part of the current moment. We can also first use the now() function to obtain the current date and time, and then use the date() function to convert the date and time into the date part. The specific code is as follows:

select date(now())

By running the above code, you will get the same result as the curdate() function.

We can also get only the year in the date using the year() function. The specific code is as follows:

select year(now())

By running the above code, the final result is 2019.

We can also get only the month in the date using the month() function. The specific code is as follows:

select month(now())

By running the above code, the final result is 12.

We can also get only the day of the date using the day() function. The specific code is as follows:

select day(now())

By running the above code, the final result is 25.

1.3 Get the current time

In addition to the need to obtain only the current date, we also have the need to obtain only the current time. If we want to get only the current time, we only need to replace the curdate() function that only gets the current date with the curtime() function. The specific code is as follows:

select curtime()

By running the above code, you can get the time part of the current moment: 22:47:37.

We can also first use the now() function to get the current date and time, and then use the time() function to convert the date and time into the time part. The specific code is as follows:

select time(now())

By running the above code, you will get the same result as the curdate() function.

We can also get only the hour in the time using the hour() function. The specific code is as follows:

select hour(now())

By running the above code, the final result is 22.

We can also get only the minutes in the time using the minute() function. The specific code is as follows:

select minute(now())

By running the above code, the final result is 47.

We can also get only the seconds in the time using the second() function. The specific code is as follows:

select second(now())

By running the above code, the final result is 37.

1.4 Get the current week number

Above we talked about how to get the current date and time, date, and time. In this section, we will look at how to get the week number of the current moment. We usually divide the whole year into 52 weeks (365 days/7), and sometimes it may be 53 weeks. If we want to see the current week of the year, we can use the weekofyear() function. The specific code is as follows:

select weekofyear(now())

By running the above code, the final result is 52.

In addition to getting the current week number of the year, we also need to get the day of the week that the current day is. The dayofweek() function used in Sql, the specific code is as follows:

select dayofweek(now())

By running the above code, the final result is 5. December 25, 2019 should be Thursday. Why is the result 5? This is because the week in this function starts from Sunday, that is, Sunday corresponds to 1, Monday corresponds to 2, and so on, so Thursday corresponds to 5.

2. Date and time format conversion

We know that the same date and time can be expressed in many different ways, and sometimes we need to convert between different formats. In Sql, we use the date_format() function. The format of the date_format function is as follows:

date_format(datetime,format)

datetime indicates the specific date and time to be converted, and format indicates the format to be converted. The optional formats are as follows:

select date_format("2019-12-25 22:47:37","%Y-%m-%d")

By running the above code, it will return the 4-digit year, the month from 01 to 12, and the day from 01 to 31, separated by -, that is, 2019-12-25.

Here we need to pay attention to the difference between 1 and 01. Essentially, they both represent 1, but the display will be slightly different. For example, in the following code, our original date is 2019-1-1, but the returned result is 2019-01-01.

select date_format("2019-1-1 22:47:37","%Y-%m-%d")
select date_format("2019-12-25 22:47:37","%H:%i:%S")

By running the above code, it will return the hour from 00-23, the minute from 00-59, and the second from 00-59, separated by :, that is, 22:47:37.

In addition to the date_format() function, there is another function extract, which is used to return a single part of a specific date and time, such as year, month, day, hour, minute, etc. The specific form is as follows:

extract(unit from datetime)

datetime represents a specific date and time, and unit represents the individual part to be returned from datetime. The unit value can be the following:

select
 extract(year from "2019-12-25 22:47:37") as col1
 ,extract(month from "2019-12-25 22:47:37") as col2
 ,extract(day from "2019-12-25 22:47:37") as col3

By running the above code, the year, month, and day in datetime will be obtained respectively. The specific results are as follows:

3. Date and time operations

Sometimes we also need to perform calculations between dates. For example, if I want to get the date 7 days before today, or the date 13 days after today, I can look up the calendar or count, but these methods are definitely not the most direct methods. Therefore, operations between dates are required.

3.1 Backward shift time

For example, if we want to get the date x days after today, it is equivalent to adding x days to today's date. We call this backward offset. At this time, we can use the date_add() function. The specific form is as follows:

date_add(date,interval num unit)

date represents the current date, or the current date and time; interval is a fixed parameter; num is the x mentioned above; unit represents the unit you want to add, whether to move back 7 days, 7 months, or 7 years. The optional values ​​are the same as the optional values ​​of unit in the extract function.

select 
 "2019-01-01" as col1
 ,date_add("2019-01-01",interval 7 year) as col2
 ,date_add("2019-01-01",interval 7 month) as col3
 ,date_add("2019-01-01",interval 7 day) as col4

By running the above code, the date corresponding to 7 years, 7 months, and 7 days after 2019-01-01 will be returned. The specific results are as follows:

select 
 "2019-01-01 01:01:01" as col1
 ,date_add("2019-01-01 01:01:01",interval 7 hour) as col2
 ,date_add("2019-01-01 01:01:01",interval 7 minute) as col3
 ,date_add("2019-01-01 01:01:01",interval 7 second) as col4

By running the above code, the date corresponding to 7 hours, 7 minutes, and 7 seconds after 2019-01-01 01:01:01 will be returned. The specific results are as follows:

3.2 Forward shift time

Where there is a backwards deviation, there will be a forwards deviation. For example, if we want to get a number of days before today, it is equivalent to subtracting x days from the current date. At this time, we use the date_sub() function. The function form of date_sub is the same as date_add. Replacing date_add in the above code with date_sub means shifting forward.

select 
 "2019-01-01" as col1
 ,date_sub("2019-01-01",interval 7 year) as col2
 ,date_sub("2019-01-01",interval 7 month) as col3
 ,date_sub("2019-01-01",interval 7 day) as col4

By running the above code, the date corresponding to 7 years, 7 months, and 7 days before 2019-01-01 will be returned. The specific results are as follows:

To shift the specified time forward, in addition to using date_sub, we can continue to use date_add, but just replace the specific num value to be added with a negative number, such as 7 with -7. The specific implementation code is as follows:

select 
 "2019-01-01" as col1
 ,date_add("2019-01-01",interval -7 year) as col2
 ,date_add("2019-01-01",interval -7 month) as col3
 ,date_add("2019-01-01",interval -7 day) as col4

The results obtained by running the above are consistent with the results obtained using date_sub.

3.3 Difference between two dates

We have discussed forward offset and backward offset above. Sometimes we also need to get the difference between two dates. We use the datediff() function. datediff is used to return the number of days between two dates. The function form is as follows:

datediff(end_date,start_date)

We use end_date to subtract start_date.

select datediff("2019-01-07","2019-01-01")

By running the above code, the difference in days between 2019-01-07 and 2019-01-01 will be returned, which is 6.

You can also watch:

What is the execution order of Sql?

This is the end of this article about MySQL date and time functions. For more information about MySQL date and time functions, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!