MySQL DATE Functions

Introduction

Dates and times are very important pieces of information for most if not all companies and applications in the world. Let us take an example of a website like Twitter. Each tweet that you make shows the exact date and time when that tweet was published. The same goes with all your comments and the same thing happens for millions of users of Twitter. Now, this information is very important for Twitter because it enables them to identify which user is very active and which users are not. It allows users to go to someone's profile and search tweets that were posted by that person on a particular date.

What about your food delivery apps, every order that you place has a date and time stamp attached to it. It not only helps to keep track of all your past orders but also helps to calculate how long it took for the order to reach you. The company can also use this information to decide which users are super active on their app so that they can push some special discounts for them.

These are just a few of the many examples that show the importance of date-time information. But understanding different date time formats can be a little complex, especially for beginners and that is why MySQL provides several built-in functions to manage and manipulate date time formats.

MySQL date functions are capable of performing simple tasks such as adding days to date as well as complex tasks such as tracking events, scheduling tasks, and organizing information.

But before we move ahead and look at different MySQL date functions, first we need to understand the MySQL date data types.

For storing a date or a date/time value in MySQL, the following data types are available:

• DATE - The date format in MYSQL is - YYYY-MM-DD
• DATETIME - Datetime format in MYSQL is - YYYY-MM-DD HH:MI
• TIMESTAMP - Timestamp format in MYSQL is - YYYY-MM-DD HH:MI
• YEAR - The Year format in MYSQL is - YYYY or YY

Syntax

The basic syntax for MySQL date functions looks something like this

The type and number of arguments mentioned here can be different depending on the function used. For example, the DATE function takes one argument, which can be a string, a number, or a date/time value. The DATEDIFF function takes two date/time values as arguments.

Usage of Date Functions

MySQL DATE functions can be used in different contexts and can perform different tasks ranging from simple manipulations to complex time calculations. Here are Some of the most common use cases include:

• Formatting dates:
  MySQL DATE functions can be used to format date values into different string representations. This can be useful for displaying dates in different formats, such as DD/MM/YYYY or YYYY-MM-DD.

  For example, let us suppose you have a table with date time entries in the format YYYY-MM-DD HH:MI:SS, one sample record can be 2019-02-23 12:04:23. Now, this whole expression can be converted to something easier to understand like Sat, 2019 February 23 12:04:23 with the help of MySQL date functions such as DATE_FORMAt(). We will learn about this function in detail later in this article.

• Calculating time intervals:
  MySQL date functions can be used to calculate the difference between two dates, or to add or subtract a certain amount of time from a date value. This can be useful for calculating durations, deadlines, or schedules. We have already seen in a previous example how many foods and other delivery websites and apps use this for their operations.

• Filtering and sorting data:
  MySQL date functions can filter or sort data based on date values. This can be useful for retrieving data from a specific date range or for sorting data chronologically. We have seen how this can happen in the Twitter example that we discussed above. A user can filter through tweets made by someone else and can see what they tweeted on a particular day.

Date-Related Function

It is time to look at some of the MySQL date functions that you might find yourself using regularly. We will look at what a particular function does, how you can use it, and look at some examples to understand the working of these functions thoroughly.

So without wasting any more time, let us get into it.

CURDATE OR CURRENT_DATE

The CURDATE OR CURRENT_DATE function in MySQL is one of the simplest date functions. It is used to get today's date in a YYYY-MM-DD or a YYYYMMDD format. The result will be a date format string or numeric value based on the context that the function used.

Both the CURDATE() and CURRENT_DATE() are synonymous and will produce the same result.

Syntax:

The syntax for the MySQL CURDATE OR CURRENT_DATE function is as follows:

Example:

Let us look at an example where we are simply printing today's date on the screen. We will be using Result as an alias for your CURDATE() function.

Output:

Now let us look at an interesting property of the MySQL CURDATE() function. We can also use the MySQL CURDATE() functions in a numeric context. So we can apply addition or subtraction operations on this function and we will get a numerical value in return

The output of the above code will be:

As you can see, the current date has been converted into a numeric value and the following addition operation has been applied to it.

DATE

The DATE function takes one argument, which can be anything from a string, a number, or a date/time value. this function will then extract the date part from the argument and returns it as a DATE value.

Syntax:

The Syntax for the MySQL DATE() function is as follows

Here, argument1 is the expression that we want to extract the data from.

Example:

Let us look at an example where we are simply using the MySQL DATE() function.

Output:

As you can see, only the part of the argument that represented the date is displayed in the result.

DATE_ADD or ADDDATE

Both the DATE_ADD or ADDDATE function adds a certain amount of time to a date value. It takes two arguments: the date value and an interval value. The interval value can be expressed as several days, months, or years.

Both the DATE_ADD or ADDDATE() function names are synonymous and will produce the same result. One minor difference between the two is that the ADDDATE() function also has a second or you can say an alternative syntax that we will look at shortly. This alternative syntax is exclusive to the ADDDATE() method and won't work with the DATE_ADD() method.

Syntax:

The MySQL DATE_ADD or the ADDDATE() function has the following syntax:

Here,

• date:
  It is the string or expression that we want to add to our interval in. It can be DATE (YEAR, MONTH, and DAY), DATETIME (HOURS, MINUTES, SECONDS), or, TIMESTAMP.
• expr:
  This is the value that represents the period that we want to add to our date.
• unit:
  This is the type of the expr value. It can be simple like DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND, or mixed values such as SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

The ADDDATE() function also has another Syntax which is as follows:

Here,

• expr:
  It is the value or string representing the date.
• days:
  It is the value representing the interval or number of days that will be added to the date.

Example:

Let us look at a very basic example of the DATE_ADD or the ADDDATE() function in MySQL

Output:

As we can notice, an interval of 1 day has been added to the date that was passed as the first argument, and the resulting new date is displayed in the result.

Now let us run the same query with the ADDDATE function

Output:

Just as we expected, we get the same result.

Here is an example of the second syntax for the ADDDATE() function.

Output:

584 days have been added to the passed date and the new resulting state is displayed in the result.

But what if we run the same query with the DATE_ADD() function

Output:

As you can see, the alternative syntax doesn't work with the DATE_ADD() method as it is exclusive to the ADDDATE() method.

DATE_FORMAT

The DATE_FORMAT function formats a date value into a string representation. It takes two arguments: the date value and the format string. The format string can contain different format specifiers. The DATE_FORMAT function formats the given date according to the format specifiers and returns the resulting date.

Syntax:

The syntax for the MySQL DATE_FORMAT() function is as follows:

Here,

• date:
  It is the date that we want to format.
• format:
  It is the expression specifying rules on how to format the given date or datetime.

Certain characters in MySQL have predefined meaning that you can use to create your format. You can find them in this table.

Example:

Let us look at some basic examples for the MySQL DATE_FORMAT() method.

Output:

Output:

As you can see, the dates have been formatted according to the format that was provided in both examples. There are a lot of ways you can format your dates or Datetime so feel free to use the table and play around with different formats.

DATE_SUB or SUBDATE

The DATE_SUB or SUBDATE function subtracts a certain amount of time from a date value. It takes two arguments: the date value and an interval value. The interval value can be expressed as several days, months, or years.

DATE_SUB and SUBDATE() are synonymous functions so they will produce the same result. They work exactly like the ADDDATE or DATE_ADD function where instead of adding an interval to the date, this function subtracts it. Another thing that the SUBDATE function has in common with the ADDDATE() function is that it also has an alternative syntax that is exclusive to SUBDATE and doesn't work with DATE_SUB.

Syntax:

The MySQL DATE_SUB or the SUBDATE() function has the following syntax:

Here,

• date:
  It is the string or expression that we want to subtract our interval from. It can be DATE (YEAR, MONTH, and DAY), DATETIME (HOURS, MINUTES, SECONDS), or, TIMESTAMP.
• expr:
  This is the value that represents the period that we want to subtract from our date.
• unit:
  This is the type of the expr value. It can be simple like DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND, or mixed values such as SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

The SUBDATE() function also has another Syntax which is as follows:

Here,

• expr:
  It is the value or string representing the date.
• days:
  It is the value representing the interval or number of days that will be subtracted from the date.

Example:

Let us look at a very basic example of the DATE_SUB or the SUBDATE() function in MySQL

Output:

As we can notice, an interval of 1 day has been subtracted from the date that was passed as the first argument, and the resulting new date is displayed in the result.

Now let us run the same query with the SUBDATE function

Output:

Just as we expected, we get the same result.

Here is an example of the second syntax for the SUBDATE() function.

Output:

584 days have been subtracted from the passed date and the new resulting state is displayed in the result.

But what if we run the same query with the DATE_SUB() function

Output:

As you can see, the alternative syntax doesn't work with the DATE_SUB() method as it is exclusive to the SUBDATE() method.

DATEDIFF

The DATEDIFF function calculates the difference between two date values, in days. It takes two arguments: the two date values. You can also pass two date-time values as parameters but the DATEDIFF() function will only consider the date part for calculating results.

Syntax:

The syntax for the MySQL DATEDIFF() function is as follows:

expr1 and expr2 represent two date or datetime values.

Example:

Let us look at some examples of the MySQL DATEDIFF() function

Output:

As you can see, the difference between the 2 dates in days is calculated and displayed in the result.

EXTRACT

The EXTRACT function extracts a specific part of a date value, such as the year, month, or day.

Syntax:

The syntax for the MySQL EXTRACT() function is as follows:

Here,

• unit:
  This is the type of the expr value. It can be simple like DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND, or mixed values such as SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.
• expr:
  It is a date or date time value that you want to extract the specific part of.

Example:

Let us look at some basic examples of the MySQL EXTRACT() function

Output:

We can see that the day part of the date string has been extracted and displayed in the result.

GET_FORMAT

The GET_FORMAT function returns the format string for specific date, time, or datetime values. The result of this function is made up of the same predefined character combinations that we saw in the DATE_FORMAT() function section.

Syntax:

The syntax for the MySQL GET_FORMAT() function is as follows.

here,

• data_type:
  This can be a DATE, a TIME, or a DATETIME value.
• standard_format:
  This represents the form and can be EUR, INTERNAL, ISO, JIS, or USA.

Just like the DATE_FORMAT() function, different function calls will have different results according to the format. You can find them in the table below.

Example:

Let us look at a simple example of the MySQL GET_FORMAT() function.

Output:

MAKEDATE

The MAKEDATE function creates a date value from a year and a day of the year value. If you enter a value that is less than 0 for the day of the year argument, then the MAKEDATE function will return NULL as a result.

Syntax:

The syntax for the MySQL MAKEDATE() function is as follows.

Example:

Now, let us see an example of the MySQL MAKEDATE() function.

Output:

As you can see, the MySQL MAKEDATE() function calculated the 314th day of the year 2012, and the date for that day is displayed in the results.

STR_TO_DATE

The STR_TO_DATE function converts a string value into a date value, based on a specific format string. The format is made up of the same character combination that we saw in the DATE_FORMAT() section.

Syntax:

The syntax for the MySQL STR_TO_DATE() function is as follows.

Example:

Let us see an example of the MySQL STR_TO_DATE() function.

Output:

you can notice that the string passed as the first argument has been converted into a date per the format passed as the second argument and the resulting data is displayed in the result set.

SYSDATE

The SYSDATE function returns the current date and time value. It does not take any arguments. The returned value has a DATETIME data type.

This function is very similar to the NOW() function which also returns the same thing. The only difference is that the SYSDATE() function returns the time when it executes whereas the NOW() function returns the time when it began to execute.

Syntax:

The syntax for the MySQL SYSDATE() function is as follows.

Example:

Let us see an example of the MySQL SYSDATE() function.

Output:

UTC_DATE

The UTC_DATE function returns the current date value in the UTC zone. It does not take any arguments. The returned value has a DATE data type.

Syntax:

The syntax for the MySQL UTC_DATE() function is as follows.

Example:

Let us see an example of the MySQL SYSDATE() function.

Output: