MySQL Date Functions: Complete Analyst’s Guide

Master MySQL date functions and acquire a powerful tool to work with date and time information in your data analyses.

Working with date and time data is an essential part of data analysis. Here are just three examples:

• Analyzing historical time series data is crucial for discovering trends and making reliable forecasts based on chronological information.
• Stock market analysis examines time series data extensively, especially when automated trading algorithms are employed.
• Meteorologists use time series data analysis to predict everything from tomorrow’s weather forecast to climate changes over the next decades.

Of course, all this data analysis is much easier when you use a tool like MySQL. In this article, we will look at MySQL’s main date and time functions. If you plan to use MySQL databases in your data analyses, then you must be able to handle MySQL date functions as deftly as a sushi chef handles their knives.

I’ll also provide links to MySQL recipes in the LearnSQL cookbook. Each cookbook article shows a problem solved with MySQL date functions and explains how to use them. I recommend that you bookmark the recipes in our cookbook for later reference.

If you are new to using MySQL functions, I recommend starting with our SQL from A to Z in MySQL learning track. It contains 7 interactive MySQL courses that will teach you the syntax, functions, and concepts you’ll need to use MySQL professionally.

Also, check out our MySQL Cheat Sheet for a quick guide to common MySQL syntax and statements.

Date and Time Datatypes in MySQL

To do time series analysis in MySQL, you must understand this language’s data types. It is essential to know the differences between types and what each one is used for. Only then can you apply them correctly in your data analysis work.

DATE

The DATE type in MySQL is used for date values that do not specify a particular time, so it is useful for representing date values where the time is irrelevant. For example, the date of a receipt or an invoice does not need to specify a particular time. The DATE type accepts a date range from 1/1/1000 to 12/31/9999.

In MySQL, literal date values are expressed as strings enclosed in single quotes. They use a year-month-day format, with 4 digits for the year, 2 for the month, and 2 for the day. For example, the date Feb 25, 2024 is expressed as '2024-02-25'.

To specify a literal date value, you can omit the hyphens in the string (e.g. '20231231') or you can use an integer value (e.g. 20231231) In these cases, you must be sure that the string or numeric value makes sense as a date value.

DATETIME

The DATETIME type is used for values that specify a date and a time. It is useful for representing the date and time of an event, such as an appointment in a doctor’s office. The date range supported by DATETIME is the same as DATE, while the time range is 00:00:00 to 23:59:59.

Datetime literals maintain the same format as date literal values. However, they also store hours, minutes, and seconds in the 24-hour format. There are two digits for each time part, and parts are separated by a colon. For example, the literal value '2024-02-25 15:45:12' expresses the date Feb 25, 2024 at 3:45:12 PM.

TIMESTAMP

The TIMESTAMP type also records date and time values. It is used to record the exact time at which certain events occur, such as when users log into a system.

There are some differences between DATETIME and TIMESTAMP, such as the range. TIMESTAMP covers a smaller range than DATETIME – between 1/1/1970 00:00:01 and 1/19/2038 03:14:07 UTC (Coordinated Universal Time). However, TIMESTAMP can store time zone information, which DATETIME cannot. There are also some differences in performance and storage space that you will have to take into account in situations where either of these aspects is critical.

TIME

A value of type TIME in MySQL not only indicates a time of day – between 00:00 and 23:59 – but also arbitrary amounts of hours (with minutes and seconds). That is why it can represent time values between -838:59:59:59 and 838:59:59:59. The value corresponding to seconds can include a fractional part with 6 digits in total. This data type can represent values with precision down to microseconds.

Literal TIME values are specified in single quotation marks, with the values of hours, minutes, and seconds separated by colons. In this sense, '10:15:15' indicates 15 minutes and 15 seconds past 10 AM.

In this form of representation, the seconds can be omitted. For example, '11:12' represents 12 minutes after 11. If the colon is not included, things can become a bit confusing; MySQL assumes that the digits on the right are seconds, then the next two digits to the left are minutes followed by hours. For example, the number '1112' converted to a TIME value indicates 0 hours, 11 minutes, and 12 seconds.

YEAR

The YEAR data type represents four-digit year values ranging from 1901 to 2155. One-digit or two-digit numeric literals can also be converted to a YEAR data type. A Y2K conversion is used for this, with the criterion that any value between 0 and 69 becomes a YEAR between 2000 and 2069 and any value between 70 and 99 becomes a YEAR between 1970 and 1999.

Converting Character Strings to Dates in MySQL

The SQL date and time data types shown above greatly facilitate the task of analyzing chronological information. But as a data analyst, you will not always be lucky enough to find the information expressed with the exact date or time data types you need. In particular, you will often find the need to work with dates or times contained in character strings. This topic is beyond the scope of this article, but you can learn how to get the time from a string in MySQL to solve this need.

Comparing and Sorting Date and Time Values in MySQL

When working with date and time data, you will very often need to compare and sort date and time type values. This allows you to segment the data by time intervals and make projections for future dates.

To learn how to make comparisons with DATE values, we will use a table called FollowersByDate that contains daily variations in the number of followers of a social network account. Here’s a sample of the table data:

In MySQL, comparing date and time values involves setting logical conditions in WHERE clauses.

This query returns all rows between Dec. 1, 2023 and Dec. 31, 2023:

SELECT *
FROM FollowersByDate
WHERE MeasureDate >= '2023-12-01' AND MeasureDate < '2024-01-01';

The comparison operators in the WHERE clause (>= and <) determine precedence between the compared date values. If the comparison Date1 <= Date2 yields True, it means that Date1 is earlier than or coincident with Date2. And if the comparison Date1 >= Date2 yields True, it indicates that Date1 is later than or coincident with Date2.

Note that the upper limit in our comparison is MeasureDate < '2024-01-01'.  We wrote the condition this way so that it works for all date and time data types – DATE, DATETIME, or TIMESTAMP. For date and time data types, the date '2024-01-01' resolves to '2024-01-01 00:00:00'. If a row had MeasureDate = '2023-12-31 15:50', it will still appear in the query result; '2023-12-31 15:50' is smaller than '2024-01-01 00:00:00'.

However, it would not appear in the result if we wrote the condition like this …

MeasureDate <= '2023-12-31'

… since '2023-12-31 15:50' is greater than '2023-12-31 00:00:00'.

For more details, I suggest you read this cookbook on comparing two dates in the WHERE clause. To broaden your knowledge of date comparison in MySQL, check out how to compare date values in SQL.

Comparing Time Values in MySQL

To compare TIME values, the columns you use in the comparisons must be of the DATETIME or TIMESTAMP type (contrary to what we saw above for comparing date values). If you use literal values to make those comparisons, it’s best to include hours, minutes, and seconds to make sure that the time interval does not exclude part of the results.

Consider a table called UserActivity that logs user activity within a system, recording the date and time of each event, the ID of the user that triggered it, and the event type ID:

To see all the events that occurred between 1:00 PM and 2:00 PM on Jan 18, 2024, you can execute the following statement:

SELECT *
FROM UserActivity
WHERE EventTimeStamp >='2024-01-18 13:00' AND EventTimeStamp <= '2024-01-18 14:00';

Note the difference between the literal values in this example and those in the previous example. Since we’re dealing with timestamp values instead of dates here, we need to specify the time values that limit the range we want. By not specifying values for the seconds in the second literal value, MySQL assumes that the range top boundary is 2:00 PM and zero seconds.

It is important to note this peculiarity. If an event was logged at 2:00:20 PM, that event would not appear in the SELECT results. For more information, read the cookbook on how to compare DATETIME values in SQL.

Sorting Date and Time Values in MySQL

To return chronologically sorted results, you must learn how to sort the result rows in a date field. You should not assume that the data resulting from a SELECT statement will appear in any particular order unless you specify that order with ORDER BY. Furthermore, you must include in ORDER BY the same columns you placed in the WHERE clause. This is seen in the following statements:

SELECT *
FROM FollowersByDate
WHERE MeasureDate >=  '2023-12-01' AND MeasureDate < '2024-01-01'
ORDER BY MeasureDate ASC;

SELECT *
FROM UserActivity
WHERE EventTimeStamp >= '2024-01-18 13:00' AND EventTimeStamp <= '2024-01-18 14:00'
ORDER BY EventTimeStamp ASC;

By using a DATE, DATETIME, or TIMESTAMP column as ordering criteria in the ORDER BY clause, you are telling MySQL that you want the query results ordered chronologically by that column. The rows are sorted from oldest to newest by default, but you can set that explicitly with the ASC (short for “ascending”) modifier. If you want the results sorted from newest to oldest, you have to use DESC (descending) instead of ASC.

Extracting Parts of a Date

In time series data analysis, you’ll frequently use parts of a date: day, month, year, and quarter. Two common cases are to obtain the evolution of quarterly sales figures over a year and the comparison of sales for the same quarter over successive years.

MySQL offers several useful functions to extract parts of a date: DAY(), MONTH(), YEAR(), QUARTER() and EXTRACT(). You can inspect the cookbooks linked in the following paragraphs for details.

Let’s go back to our FollowersByDate table. We can use the DAY() function to get the day of the month for each date. Then we can use it in a GROUP BY clause to learn about trends in follower behavior by the day of the month:

SELECT DAY(MeasureDate) AS DayOfMonth, SUM(NewFollowers) AS NewFollowers
FROM FollowersByDate
group BY DAY(MeasureDate);

The previous query will return something like this:

The EXTRACT() function works similarly to DAY(), MONTH(), YEAR(), and QUARTER(), but it requires that you specify which part of the date you want to extract. The general syntax is

EXTRACT([the date part you want to extract] FROM [the date or datetime value from which you want to extract a part]). 

There are many options to specify the date part – including combinations of different date parts, such as DAY_HOUR, HOUR_MINUTE, MINUTE_SECOND, and others. By using EXTRACT() with DAY_HOUR on the UserActivity table, you can detect usage patterns based on the day of the month and the time of day:

SELECT
  EXTRACT(DAY_HOUR FROM EventTimeStamp) AS DayAndHour,
  COUNT(*) AS EventCount
FROM UserActivity
GROUP BY EXTRACT(DAY_HOUR FROM EventTimeStamp);

Combined date part values are expressed as a concatenation of the individual date part values involved. For example, in the case of DAY_HOUR, 1422 means the 14th day of the month at 10 PM. The above query will return something like this:

Ordering and Grouping by the Month

When you need to group data by month, you first need to get the month from a date. For this, you will need to use the MONTH() function, which you can place in the GROUP BY, WHERE, or ORDER BY clauses to manipulate the information as you wish. The month extraction can be applied to values of type DATE, DATETIME, or TIMESTAMP.

Let’s see some examples. The following query on the FollowersByDate table allows you to find out how many new followers joined in each month of the year 2023. It is an example of how to group data by month in MySQL:

SELECT MONTH(MeasureDate) AS Month_No, SUM(NewFollowers) AS New_Followers
FROM FollowersByDate
WHERE MeasureDate >= '2023-01-01' AND MeasureDate < '2024-01-01'
GROUP BY MONTH(MeasureDate)
ORDER BY MONTH(MeasureDate);

The result will look like this:

In the above query, we use the MeasureDate column in the WHERE clause to obtain only the results for the year 2023. With the MONTH() function, we group and sort the results by month.

Ordering and Grouping by Year in MySQL

If you would like to sort and group values by year, just replace the MONTH() function with the YEAR() function:

SELECT YEAR(MeasureDate) AS Year, SUM(NewFollowers) AS New_Followers
FROM FollowersByDate
GROUP BY YEAR(MeasureDate)
ORDER BY YEAR(MeasureDate);

We’ve removed the conditions on the date and time fields in the WHERE clause, since we are interested in seeing the information grouped by year for the whole table. For more information, read about how to get the year from a datetime column in MySQL.

Ordering and Grouping by Year and Month in MySQL

In a previous example, we saw how to group by the month. The problem with this technique is that, if you remove the date range from the WHERE clause, the results of grouping only by month will combine information from all the years that exist in the table.

To avoid this drawback and group by calendar month in MySQL, you need to use a combination of month and year. This can be done by extracting the combined year and month from a datetime column. You can use the EXTRACT function with the YEAR_MONTH flag for this, which greatly simplifies queries when you have to deal with calendar months:

SELECT
    EXTRACT(YEAR_MONTH FROM MeasureDate) AS YearMonth,
    SUM(NewFollowers) AS New_Followers
FROM FollowersByDate
GROUP BY EXTRACT(YEAR_MONTH FROM MeasureDate)
ORDER BY EXTRACT(YEAR_MONTH FROM MeasureDate);

You can also group and order by both the year and the month:

SELECT 
  YEAR(MeasureDate) AS Year, 
  MONTH(MeasureDate) AS Month, 
  SUM(NewFollowers) AS New_Followers
FROM FollowersByDate
GROUP BY YEAR(MeasureDate), MONTH(MeasureDate)
ORDER BY YEAR(MeasureDate), MONTH(MeasureDate);

Formatting Dates for Better Readability

To show a user the content of a column or variable of the DATE or DATETIME types, it is essential to format your query in a way that makes the data understandable and neatly displayed.

There are many options for formatting a date. In MySQL, you use the DATE_FORMAT function. It receives a date parameter and a string with format descriptors that allow you to format the date in any way you want.

To show the dates in a readable format – that includes weekday, year, month name, and day number – you must use the DATE_FORMAT function as follows:

SELECT
    DATE_FORMAT(MeasureDate, '%a, %Y %M %e') AS Formatted_Date,
    NewFollowers
FROM FollowersByDate
ORDER BY MeasureDate;

The format descriptors used in this example provide for dates to be displayed with the abbreviated weekday name (%a) first, then a comma, then the year as a four-digit value (%Y), then the month name in full (%M), and finally the day of the month as a numeric value (%e). The resulting data looks like this:

Notice that we include the MeasureDate column in the ORDER BY clause without enclosing it in the DATE_FORMAT() function. This is necessary for the results to be chronologically ordered. If we were to place the column inside the DATE_FORMAT() function as it is in the SELECT column list, the results would not be in chronological order – the ordering would be done according to the alphabetical order of the dates formatted as character strings.

For more information on date formatting, read this cookbook on the use of the DATE_FORMAT function.

For analysis tasks that deal with cyclic (i.e., weekly) behaviors, it is useful to know how to obtain the day of the week from a date. In the following example, we use the weekday of each event registered in the UserActivity table to find out the number of times each event occurs according to the day of the week. In this way, we can obtain patterns of user behavior:

SELECT
   DAYNAME(EventTimeStamp) AS WeekDay,
   EventTypeId,
   COUNT(*) AS TotalEvents
FROM UserActivity
GROUP BY DAYNAME(EventTimeStamp), EventTypeId;

Date and Time Arithmetic

A common need in time series data analysis is to perform arithmetic operations with date and time values. This means adding or subtracting time – expressed in any time unit – to a date and time value. It’s calculating the difference between two date/time values and expressing that difference in whatever time unit is needed.

If you want to use the data from our UserActivity table to find out all the users who had any activity during the last 3 days, you need to add days to a date using the appropriate MySQL function. In this example, you subtract days (or, more precisely, add a negative number) to get a resulting date in the past. You also need a MySQL function to get the current time and subtract days from it. The following SELECT statement allows you to get such information:

SELECT DISTINCT UserId
FROM UserActivity
WHERE EventTimeStamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -3 DAY);

Note that the use of the DATE_ADD() function with a negative number of days in the WHERE clause allows using a past date to filter the results. The function CURRENT_TIMESTAMP requires no arguments and always returns a TIMESTAMP value expressing the current date and time of the database server.

Similarly, to get the total number of new followers in the last 3 days, you can use a MySQL current date function (i.e. CURDATE()) and subtract 3 days from it:

SELECT SUM(NewFollowers)
FROM FollowersByDate
WHERE MeasureDate  >= DATE_ADD(CURDATE(), INTERVAL -3 DAY);

Adding or Subtracting with DATETIME

You may need to add time units to a DATETIME value. Suppose you want to make a query similar to the previous one – but instead of considering the last 3 days, you want to know which users have had activity in the last 3 hours. To do that, you need to know how to add time to a DATETIME value. As in the previous example, you must add a negative value to obtain the TIMESTAMP of a moment in the past. With the following SELECT statement, you can get that information:

SELECT DISTINCT UserId
FROM UserActivity
WHERE EventTimeStamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -3 HOUR);

Calculating Differences Between Dates and Times

Date and time arithmetic also involves calculating differences between date and time values. In MySQL, you can use the DATEDIFF() function to find the difference between two dates. It returns an integer value expressing the number of days between the oldest and the most recent date.

For example, imagine you have a table called Users that records the last time each user changed their password:

A query like the following will return the number of days each user has gone without changing their password:

SELECT
    UserName,
    DATEDIFF(CURRENT_DATE, LastPassChange) AS DaysSinceLastPassChange
FROM Users;

Also, the MySQL TIMESTAMPDIFF() function allows you to calculate the time difference between two timestamp values. This function requires you to specify the unit of time used to express the difference. This can be from microseconds to years.

Suppose you have a table called ExecLog that saves the execution log of a scheduled task, with an indication of when each execution started and when it ended:

You can determine the number of seconds each execution took with this SELECT statement:

SELECT
    ExecId,
    TIMESTAMPDIFF(SECOND, TimeStarted, TimeEnded) AS SecondsLasted
FROM ExecLog;

Obtaining the number of seconds per execution may be useful for processes that complete quickly – i.e. less than one minute. But for long processes, which may take several hours to run, the number of seconds of execution may be of little use. In such cases, you’d use a MySQL function to transform seconds into another time value.

In the following example, we use the MySQL function SEC_TO_TIME() in combination with TIMESTAMPDIFF() to obtain the amount of execution time for each process. This difference is expressed in a readable form:

SELECT
    ExecId,
    SEC_TO_TIME(TIMESTAMPDIFF(second, TimeEnded, TimeStarted)) AS Exec_Time
FROM ExecLog;

Getting Yesterday’s Date

You’ll often need to obtain summarized data up to the current date. However, you’ll usually want to avoid including today’s data because it is incomplete. If you are analyzing data from the UserActivity table, today’s summarized data will not be accurate until the end of the day. That is why it will be handy for you to learn how to get yesterday’s date in MySQL. You can use this technique if you need to analyze data but keep today’s incomplete data from distorting the results:

SELECT *
FROM UserActivity
WHERE EventTimeStamp <= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

Get the Date of the Last Day of the Month

When analyzing chronological data, you’ll also often query information up to the last day of a given month. In MySQL, you can use the LAST_DAY() function to get the last day of the month for a given date.

For example, suppose you have a table called Appointments that stores a schedule of appointments for a doctor’s office. It has an AppointmentDate column that records the date of each appointment. To know all the appointments scheduled from today until the end of the current month, you can use this query:

SELECT *
FROM Appointments
WHERE AppointmentDate BETWEEN CURDATE() AND LAST_DAY(CURDATE());

Need More Practice with MySQL Date Functions?

Throughout this article, we have reviewed the tools MySQL offers to manipulate date and time values. These are essential to performing time series analyses. We have also reviewed the data types that MySQL uses to store date and time data and the functions that compare, sort, extract, group, and format date and time values.

If you feel lost on anything we’ve just covered, consider taking the learning track SQL from A to Z in MySQL. Its 7 interactive courses are ideal for in-depth practice. If you just need a quick refresher, our MySQL Cheat Sheet will remind you of important syntax and functions.

Don’t forget to visit LearnSQL.com – especially our cookbook section – for a selection of unbeatable recipes for success. With these resources, you’ll have everything you need for your time-series data analysis work.