Last updated on July 11th, 2024 at 06:49 am
MySQL is a popular database used by millions of websites and apps. It supports different data types and offers tons of features to work with them. While working with MySQL, almost everyone needs to deal with datetime information at sometime or the other. Often you may need to get date from datetime or get current datetime. In this article, we will look at how to get current date and time in MySQL.
Why Get Current Date & Time
MySQL is commonly used as a transactional database to store data about online commerce. In such cases, web developers and database programmers need to get current date and time values in order to record each transaction correctly – whether it is adding to cart, checkout, cancellation, refund, invoicing, etc. So it is important to know how to retrieve current date and time values in MySQL. Otherwise, you will not be able to track your transactions correctly.
How to Get Current Date and Time in MySQL
There are several ways to get current date and time in MySQL.
1. Using Now() Function
Now() function is a system function that directly returns current date and time, when invoked in query. It does not require any input parameters. You can run NOW() function to get current date and time in MySQL.
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-12-21 12:18:33 | +---------------------+
You can also use interval clause to add/subtract months, weeks, days, hours, minutes and seconds from the current date returned by now(), as shown below.
mysql> select now() - interval 1 month;
+--------------------------+
| now() - interval 1 month |
+--------------------------+
| 2024-06-11 11:53:38 |
+--------------------------+
mysql> select now() + interval 1 month;
+--------------------------+
| now() + interval 1 month |
+--------------------------+
| 2024-08-11 11:53:41 |
+--------------------------+
mysql> select now() - interval 1 week;
+-------------------------+
| now() - interval 1 week |
+-------------------------+
| 2024-07-04 11:53:47 |
+-------------------------+
mysql> select now() + interval 1 week;
+-------------------------+
| now() + interval 1 week |
+-------------------------+
| 2024-07-18 11:53:51 |
+-------------------------+
mysql> select now() - interval 1 day;
+------------------------+
| now() - interval 1 day |
+------------------------+
| 2024-07-10 11:53:55 |
+------------------------+
mysql> select now() + interval 1 day;
+------------------------+
| now() + interval 1 day |
+------------------------+
| 2024-07-12 11:53:59 |
+------------------------+
mysql> select now() - interval 1 hour;
+-------------------------+
| now() - interval 1 hour |
+-------------------------+
| 2024-07-11 10:54:08 |
+-------------------------+
mysql> select now() + interval 1 hour;
+-------------------------+
| now() + interval 1 hour |
+-------------------------+
| 2024-07-11 12:54:11 |
+-------------------------+
mysql> select now() - interval 1 minute;
+---------------------------+
| now() - interval 1 minute |
+---------------------------+
| 2024-07-11 11:53:19 |
+---------------------------+
mysql> select now() + interval 1 minute;
+---------------------------+
| now() + interval 1 minute |
+---------------------------+
| 2024-07-11 11:55:23 |
+---------------------------+
mysql> select now() - interval 1 second;
+---------------------------+
| now() - interval 1 second |
+---------------------------+
| 2024-07-11 11:54:26 |
+---------------------------+
mysql> select now() + interval 1 second;
+---------------------------+
| now() + interval 1 second |
+---------------------------+
| 2024-07-11 11:54:32 |
+---------------------------+
2. Using current_timestamp()
curent_timestamp() is similar to now() function. Like now() function, it returns the result as YYYY-MM-DD HH-MM-SS string. You can also use current_timestamp() to get latest date and time in MySQL.
mysql> select current_timestamp(); +---------------------+ | current_timestamp() | +---------------------+ | 2020-12-21 12:18:33 | +---------------------+
Like now(), you can use interval clause to add/subtract months, weeks, days, hours, minutes and seconds from the current date returned by current_timestamp().
3. Using current_date
If you only want to get current date in MySQL, you can use system variable current_date or functions like curdate() or current_date(). They all give latest date in YYYY-MM-DD format. Here is an example to illustrate it.
mysql> select current_date,curdate(),current_date(); +--------------+------------+----------------+ | current_date | curdate() | current_date() | +--------------+------------+----------------+ | 2020-12-21 | 2020-12-21 | 2020-12-21 | +--------------+------------+----------------+
Like in case of now(), you can use interval clause to add/subtract intervals of month, week, day, hour, minute, second from the result of current_date. But in all cases, it will only return a date and not datetime.
mysql> select current_date - interval 1 month;
+---------------------------------+
| current_date - interval 1 month |
+---------------------------------+
| 2024-06-11 |
+---------------------------------+
mysql> select current_date + interval 1 month;
+---------------------------------+
| current_date + interval 1 month |
+---------------------------------+
| 2024-08-11 |
+---------------------------------+
mysql> select current_date - interval 1 week;
+--------------------------------+
| current_date - interval 1 week |
+--------------------------------+
| 2024-07-04 |
+--------------------------------+
mysql> select current_date + interval 1 week;
+--------------------------------+
| current_date + interval 1 week |
+--------------------------------+
| 2024-07-18 |
+--------------------------------+
mysql> select current_date - interval 1 day;
+-------------------------------+
| current_date - interval 1 day |
+-------------------------------+
| 2024-07-10 |
+-------------------------------+
mysql> select current_date + interval 1 day;
+-------------------------------+
| current_date + interval 1 day |
+-------------------------------+
| 2024-07-12 |
+-------------------------------+
mysql> select current_date - interval 1 hour;
+--------------------------------+
| current_date - interval 1 hour |
+--------------------------------+
| 2024-07-10 23:00:00 |
+--------------------------------+
mysql> select current_date + interval 1 hour;
+--------------------------------+
| current_date + interval 1 hour |
+--------------------------------+
| 2024-07-11 01:00:00 |
+--------------------------------+
mysql> select current_date - interval 1 minute;
+----------------------------------+
| current_date - interval 1 minute |
+----------------------------------+
| 2024-07-10 23:59:00 |
+----------------------------------+
mysql> select current_date + interval 1 minute;
+----------------------------------+
| current_date + interval 1 minute |
+----------------------------------+
| 2024-07-11 00:01:00 |
+----------------------------------+
mysql> select current_date - interval 1 second;
+----------------------------------+
| current_date - interval 1 second |
+----------------------------------+
| 2024-07-10 23:59:59 |
+----------------------------------+
mysql> select current_date + interval 1 second;
+----------------------------------+
| current_date + interval 1 second |
+----------------------------------+
| 2024-07-11 00:00:01 |
+----------------------------------+
4. Using current_time
If you only want to get current time in MySQL, you can use system variable current_time or functions like curtime() or current_time(). They all give latest time in HH:MM:SS format
mysql> select current_time,curtime(),current_time(); +--------------+-----------+----------------+ | current_time | curtime() | current_time() | +--------------+-----------+----------------+ | 12:24:41 | 12:24:41 | 12:24:41 | +--------------+-----------+----------------+
Conclusion
MySQL provides predefined functions as well as system variables to give you current date and time. You can use either of them as per your requirement. You can also add or subtract months, weeks, days, hours, minutes and seconds from the result of these functions and system variables, if you want to calcualte a datetime value using the current datetime as baseline.
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 Group By Month in MySQL
How to Remove Default Column Value in MySQL
How to Remove NOT NULL constraint in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.