Last updated on July 23rd, 2024 at 07:53 am
MySQL is a popular database often used for storing transactional data that contains date, time and timestamp information. Sometimes you may need to round timestamp in MySQL for reporting and data analysis. In this article, we will look at different ways to round timestamp in MySQL. It allows you to transform datetime columns to various formats, as per your requirements.
Why Round Timestamp in MySQL
Timestamp is a data type used to store date and time information at one go, in a single column. They are often used to store transactional information such as sign ups, logins, sales, orders, checkouts, etc. They are generally of the format ‘YYYY-MM-DD HH:MM:SS’ (YYYY-year, MM-month, DD-day, HH-hour, MM-minute, SS-second).
Rounding a timestamp means adjusting it to the nearest, specific time period. It may be rounding to nearest year, month, day, hour, minute or even second. Data analysts and web developers need to round timestamp for data analysis and reporting, to aggregate data into regular intervals. This allows you to group continuous data into discrete buckets that can be used for charting and reporting purposes. For example, you can round sales dates to nearest month, to analyze monthly sales numbers.
Also, if you have a large table containing millions of rows of sales data, it can be overwhelming to look at it. Instead, you can simply round timestamp values to nearest month or year to get monthly or annual numbers which are far lesser in number.
Please note, in most cases, rounding of timestamp values are used in SELECT queries without modifying underlying data.
How to Round Timestamp in MySQL
There are many different ways to round timestamp values. Among them, using date_format() function is the easiest and most versatile.
Using date_format()
We will look at how to round timestamp in MySQL using date_format function. date_format function has the following syntax
date_format(value, format_string)
For date_format function, you need to specify a value that you want to convert and a format string of final value. Depending on the format string, it will change your value to required format. Here is the list of available format specifiers for date_format function.
We will also look at how to convert timestamp into hour, minute, date.
Here’s the SQL query to get present date time.
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-07-23 12:39:03 |
+---------------------+
Round Timestamp to Month
There are two ways to round a timestamp to nearest month. You can either round it down or round it up. Here is the SQL query to round down current timestamp to month.
mysql> SELECT date_format(now(),'%Y-%m') as round_month;
+-------------+
| round_month |
+-------------+
| 2024-07 |
+-------------+
Similarly, you can round timestamp column (e.g order_date from sales table) to month
mysql> SELECT date_format(order_date,'%Y-%m') from sales;
If you want to round up the current timestamp to nearest month, then you need to add 15 days to the timestamp value using date_add() or other similar function.
mysql> SELECT date_format(date_add(now(), interval 15 day),'%Y-%m') as round_month;
+-------------+
| round_month |
+-------------+
| 2024-08 |
+-------------+
Round Timestamp to Day
Here too, there are two ways to round a timestamp to a date. You can either round down or round up the value. Here is the SQL query to round down current timestamp to day.
mysql> SELECT date_format(now(),'%Y-%m-%d') as round_date;
+------------+
| round_date |
+------------+
| 2024-07-23 |
+------------+
Similarly, you can round timestamp column (e.g order_date from sales table) to day
mysql> SELECT date_format(order_date,'%Y-%m-%d') from sales;
If you want to round up the timestamp to nearest date, then you need to add 12 hours to timestamp value using date_add() or other similar functions.
mysql> SELECT date_format(date_add(now(),interval 12 hour),'%Y-%m-%d') round_date;
+------------+
| round_date |
+------------+
| 2024-07-24 |
+------------+
Round Timestamp to Hour
In this case, we will round current timestamp value to nearest hour. There are two ways to do this. You can either round down or round up the timestamp value. Here is the SQL query to round down current timestamp to hour.
mysql> SELECT date_format(now(),'%Y-%m-%d %H:00:00') as round_time;
+---------------+
| round_time |
+---------------+
| 2024-07-23 12 |
+---------------+
Similarly, you can round timestamp column (e.g order_date from sales table) to hour
mysql> SELECT date_format(order_date,'%Y-%m-%d %H') from sales;
In the above case, the timestamp value will be rounded down to nearest hour value. If you want to round up the timestamp value then add 30 minutes to your timestamp value before calling date_format() on it.
mysql> SELECT date_format(date_add(now(),interval 30 minute),'%Y-%m-%d %H:00:00') as round_time;
+---------------------+
| round_time |
+---------------------+
| 2024-07-23 13:00:00 |
+---------------------+
Round Timestamp to Minute
You can round up or round down timestamp to nearest minute. This is required if you are aggregating data on a minute by minute basis. Here is the SQL query to round down current timestamp to minute.
mysql> SELECT date_format(now(),'%Y-%m-%d %H:%i') as round_min;
+------------------+
| round_min |
+------------------+
| 2024-07-23 12:54 |
+------------------+
Similarly, you can round timestamp column (e.g order_date from sales table) to minutes
mysql> SELECT date_format(order_date,'%Y-%m-%d %H:%i') as round_min from sales;
If you want to round up timestamp value to nearest minute, add 30 seconds to timestamp value before you call date_format() function.
mysql> SELECT date_format(date_add(now(),interval 30 second),'%Y-%m-%d %H:%i') as round_min;
+------------------+
| round_min |
+------------------+
| 2024-07-23 12:55 |
+------------------+
Round Timestamp to Seconds
Here is the SQL query to round current timestamp to seconds.
mysql> SELECT date_format(now(),'%Y-%m-%d %H:%i:%s'); +----------------------------------------+ | date_format(now(),'%Y-%m-%d %H:%i:%s') | +----------------------------------------+ | 2020-12-08 11:13:10 | +----------------------------------------+
Similarly, you can round timestamp column (e.g order_date from sales table) to seconds
mysql> SELECT date_format(order_date,'%Y-%m-%d %H:%i:%s') from sales;
Conclusion
As you can see, it is very easy to change datetime and timestamp values to month, day, hour, minute and second. Depending on your requirement, you can use date_format() function, along with appropriate format string. date_format() is a very powerful function that allows you to format your date, time and timestamp values to any format you want. You can also use literal characters in addition to datetime numbers in its format.
We have learnt several common use cases. If you directly call date_format() function on your timestamp value, then it will round it down. If you want to round it up, then you need to add an interval of time to it, before you call date_format() function. In each of the above example, the actual timestamp value in table is not modified. Only the SELECT query result is modified. Also you can use these methods on date, time, datetime as well as timestamp columns.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read:
How to Rename Table in MySQL
How to Run Multiple MySQL Instances on Single Server
How to Insert Multiple Rows in MySQL
How to Update Column Based on Another Column in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.