How to use DATETIME functions in MySQL

Introduction

We will examine the various date and time functions that MySQL offers. Numerous built-in functions in MySQL let you work with data. Several choices are available in date functions to change date, time, and datetime expressions.

MySQL ADDDATE() Function

To add the desired interval to a date value, use the MYSQL ADDDATE() method.

Syntax

ADDDATE(date, interval expr unit);

Explanation

• date- Represents the value date.
• expr- Represents the interval value.
• unit- Type of interval value like day, week, quarter, month, year, hour, minute, second.

Example

SELECT ADDDATE('2023-03-25' ,INTERVAL 20 DAY);

Use case

This function can be used to determine past or future dates based on an interval. For example, if you have a payment due date and want to calculate the date 30 days after that, you can use ADDDATE() to do that easily.

MySQL ADDTIME() Function

The MYSQL ADDTIME() function is used to add the specified time interval to a date time or time value.

Syntax

ADDTIME(expr1, expr2);

Explanation

• expr1- Represents the time or datetime.
• expr2- Represents the time interval to be added.

Example

SELECT ADDTIME('12:35:32.88558', '08:05:01.222222');

Use case

This function is useful for calculating the time after adding a certain amount of time to a given time value. For instance, if you want to find out what the time will be after 3 hours and 45 minutes from now, you can use ADDTIME() to do that.

MySQL CURRENT_DATE() Function

The MySQL CURRENT_DATE() Function is used to get the current day's date.

Syntax

CURRENT_DATE();

Example

 SELECT CURRENT_DATE();

Use case

This function is used to get the current date. It's useful for displaying the current date in a particular format or for filtering records in a database based on the current date.

MySQL CURRENT_TIME() Function

The MySQL CURRENT_TIME() Function is used to get the current time.

Syntax

CURRENT_TIME();

Example

SELECT CURRENT_TIME();

Use case

This function is used to get the current time. It's useful for displaying the current time in a particular format or for filtering records in a database based on the current time.

MySQL CURRENT_TIMESTAMP Function

The MySQL CURRENT_TIMESTAMP Function is used to get the current date and time value.

Syntax

CURRENT_TIMESTAMP();

Example

SELECT CURRENT_TIMESTAMP();

Use case

This function is used to get the current date and time. It's useful for displaying the current date and time in a particular format or for filtering records in a database based on the current date and time.

MySQL DAY() Function

The MySQL DAY() Function is used to retrieve the day of the given date. This function returns the numerical value between 1 to 30.

Syntax

DAY(date);

Example

SELECT DAY('2023-03-25 09:40:45.2300');

Use case

This function is used to get the day of the month from a given date. It's useful for calculating a person's age based on their date of birth or for filtering records based on the day of the month.

MySQL HOUR() Function

The MySQL HOUR() Function is used to retrieve and return the hour from the given date-time expression.

Syntax

HOUR(time);

Example

SELECT HOUR('00 12:38:48');

Use case

This function is used to get the hour from a given time value. It's useful for calculating the number of hours between two-time values or for filtering records based on the hour of the day.

MySQL MINUTE() Function

The MySQL MINUTE() function is used to retrieve and return the minutes in the given time or date time expression. This returns a numerical value ranging from 0 to 59.

Syntax

MINUTE(time);

Example

SELECT MINUTE('00 10:35:45');

Use case

This function is used to get the minute from a given time value. It's useful for calculating the number of minutes between two-time values or for filtering records based on the minute of the hour.

MySQL MONTH() Function

The MySQL MONTH() Function is used to retrieve and return the MONTH of the given date or date time expression. This function returns a numerical value ranging from 1 to 12, representing the month (January to December).

Syntax

MONTH(date);

Use case

This function is used to get the month from a given date value. It's useful for calculating the number of months between two dates or for filtering records based on the month of the year.

MySQL NOW() Function

The MySQL NOW() Function is used to retrieve the current date and time. The returned value will be in the 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.

Syntax

NOW();

Example

SELECT NOW();

Use case

This function is used to get the current date and time. It's useful for inserting the current date and time into a table or for displaying the current date and time in a particular format.

MySQL QUARTER() Function

The MySQL QUARTER() Function is used to retrieve and return the quarter year of the given date or date time expression. This function returns a numerical value ranging from 1 to 4.

Syntax

QUARTEE(date);

Example1

SELECT QUARTER('2023-03-25);

Example2

SELECT QUARTER('2023-12-25');

Use case

This function is used to get the quarter of the year from a given date value. It's useful for calculating the number of quarters between two dates or for filtering records based on the quarter of the year.

MySQL UTC_DATE() Function

The MYSQL UTC_DATE() is used to get the current UTC date. The resultant value is a string or a numerical value based on the context, and the date returned will be in the 'YYYY-MM-DD' or YYYYMMDD format.

Syntax

UTC_DATE();

Example

SELECT UTC_DATE();

Use case

This function is used to get the current UTC date. It's useful for displaying the current UTC date in a particular format or for filtering records based on the UTC date.

MySQL UTC_TIME() Function

The MySQL UTC_TIME() Function is used to get the current UTC time. The resultant value is in the 'hh:mm:ss' format.

Syntax

UTC_TIME();

Example

SELECT UTC_TIME();

Use case

This function is used to get the current UTC time. It's useful for displaying the current UTC time in a particular format or for filtering records based on the UTC time.

MySQL YEAR() Function

The MySQL YEAR() Function is used to retrieve and return the year of the given date or date time expression. This function returns a numerical value ranging from 1000 to 9999.

Syntax

YEAR(date);

Example

SELECT YEAR('2023-03-25');

Use case

This function is used to get the year from a given date value. It's useful for calculating the age of a person based on their date of birth or for filtering records based on the year.

Conclusion

We are now familiar with the frequently used date and time functions. These functions are useful for filtering records in a database, calculating the age of a person, or displaying the current date and time in a particular format.

FAQs

Q. What is MySQL ADDDATE() Function?

A. MySQL ADDDATE() Function is a built-in function that is used to add a specific interval to a given date value.

Q. What is the syntax of the ADDDATE() Function?

A. The syntax of ADDDATE() Function is as follows: ADDDATE(date, interval expr unit);

Q. What is the use case of the ADDDATE() Function?

A. An ADDDATE() Function is useful to calculate dates in the past or the future based on an interval. It's used for calculating the payment due date, etc.

Q. What is MySQL ADDTIME() Function?

A. MySQL ADDTIME() Function is a built-in function that is used to add the specified time interval to a date time or time value.