How to Round Timestamp in MySQL

Sometimes you may need to round timestamp in MySQL. In this article, we will look at how to round timestamp in MySQL using date_format function. It allows you to transform datetime columns to various formats, as per your requirements.

How to Round Timestamp in MySQL

We will look at how to round timestamp in MySQL using 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();

date_format function has the following syntax

date_format(format_string, value)

For date_format function, you need to specify a format string and value that you want to convert. 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.

Round Timestamp to Month

Here is the SQL query to round current timestamp to month.

mysql> SELECT date_format(now(),'%Y-%m-%d %H');
+----------------------------------+
| date_format(now(),'%Y-%m')       |
+----------------------------------+
| 2020-12                          |
+----------------------------------+

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;

Round Timestamp to Day

Here is the SQL query to round current timestamp to day.

mysql> SELECT date_format(now(),'%Y-%m-%d');
+----------------------------------+
| date_format(now(),'%Y-%m-%d')    |
+----------------------------------+
| 2020-12-08 11                    |
+----------------------------------+

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;

Round Timestamp to Hour

Here is the SQL query to round current timestamp to hour.

mysql> SELECT date_format(now(),'%Y-%m-%d %H');
+----------------------------------+
| date_format(now(),'%Y-%m-%d %H') |
+----------------------------------+
| 2020-12-08 11                    |
+----------------------------------+

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;

Round Timestamp to Minute

Here is the SQL query to round current timestamp to minute.

mysql> SELECT date_format(now(),'%Y-%m-%d %H:%i');
+-------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i') |
+-------------------------------------+
| 2020-12-08 11:13                    |
+-------------------------------------+

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') from sales;

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;

As you can see, it is very easy to change datetime and timestamp values to month, day, hour, minute and second.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!