Using datetime functions in MySQL

Página 49

MySQL is a database management system that allows you to manage relational databases. It is open source software that is easy to use. One of the most powerful features of MySQL is its ability to work with dates and times. The datetime functions in MySQL are used to retrieve, manipulate, and calculate dates and times.

Let's start with the NOW() function. The NOW() function returns the current date and time. The format of the date and time returned by the NOW() function is 'YYYY-MM-DD HH:MM:SS'. For example:

SELECT NOW();

The CURDATE() function is used to get the current date. It returns the date in 'YYYY-MM-DD' format. For example:

SELECT CURDATE();

The CURTIME() function returns the current time. The time is returned in 'HH:MM:SS' format. For example:

SELECT CURTIME();

The DATE() function is used to extract the date portion of a datetime value. For example, if you have a column that contains dates and times and you only want the date, you can use the DATE() function. For example:

SELECT DATE('2021-12-31 13:31:50');

The TIME() function is used to extract the time portion of a datetime value. For example:

SELECT TIME('2021-12-31 13:31:50');

The YEAR(), MONTH() and DAY() functions are used to extract the year, month and day part of a date value, respectively. For example:

SELECT YEAR('2021-12-31'), MONTH('2021-12-31'), DAY('2021-12-31');

The HOUR(), MINUTE(), and SECOND() functions are used to extract the hour, minute, and second part of a time value, respectively. For example:

SELECT HOUR('13:31:50'), MINUTE('13:31:50'), SECOND('13:31:50');

The DATEDIFF() function is used to calculate the difference between two dates. It returns the difference in days. For example:

SELECT DATEDIFF('2021-12-31', '2021-01-01');

The TIMEDIFF() function is used to calculate the difference between two hours. It returns the difference in 'HH:MM:SS' format. For example:

SELECT TIMEDIFF('13:31:50', '10:30:40');

The DATE_ADD() function is used to add a time interval to a date. For example, to add 1 day to the date '2021-12-31', you can use the following query:

SELECT DATE_ADD('2021-12-31', INTERVAL 1 DAY);

Likewise, the DATE_SUB() function is used to subtract a time range from a date. For example:

SELECT DATE_SUB('2021-12-31', INTERVAL 1 DAY);

The date and time functions in MySQL are very useful for manipulating and calculating dates and times. They allow you to perform a wide variety of operations, such as getting the current date and time, extracting parts of a date or time, calculating the difference between two dates or times, and adding or subtracting a time range from a date.

So if you're working with data that includes dates and times, it's critical that you understand how to use these functions. With practice, you will become increasingly comfortable using these functions and be able to manipulate dates and times with ease.

Next page of the Free Ebook:

50Using String Functions in MySQL