In this tutorial, we will look at how to insert date and time records in a database. This can include when we added a specific record to the database.
MySQL now() Function
The simplest method to insert the current date and time in MySQL is to use the now() function. Once you call the function, it returns the current date and time in the system’s configured time zone as a string.
The value returned from the now() function is YYYY-MM-DD for the date and HH-MM-SS-UU for the time record.
A simple use case of the now() function is:
Once we run the above query, we should get the exact date and time when the query starts executing.
It is good to note that the now() function returns the date and time when the query starts executing. If you want the exact date and time the query completes, you can use the sysdate() function.
Example:
+---------------------+
| sysdate() |
+---------------------+
| 2021-07-26 22:12:19 |
+---------------------+
This returns the current system date and time when the query executes.
MySQL current_date()
If you want to get the current date only without including the time, you can use the curdate() function.
This function also returns the date in the form of YYYY-MM-DD as a string. For example:
+------------+
| curdate() |
+------------+
| 2021-07-26 |
+------------+
MySQL curtime() Function
Similarly, if you want to get the current time without including the date, you can use the curtime() function.
Here is an example:
+-----------+
| curtime() |
+-----------+
| 22:16:43 |
+-----------+
The current date and time depend on the set time zone. To change the time zone in MySQL, use the SET query as:
The above query should change your time zone to your preferred option.
Loading MySQL Time Zones
In some instances, when you execute the above command, especially on Windows devices, you will get an “Unknown or Incorrect Time Zone” error.
The main cause of this is the base system is not being populated with the time zones database.
To fix this, you can load the time zones database as described in the following steps:
CAUTION: Do NOT use this method on a system that comes with zoneinfo files loaded. For example, in Linux, the zoneinfo files are loaded in /usr/share/zoneinfo directory:
Step 1: Open your browser and navigate to the MySQL timezones download page:
https://dev.mysql.com/downloads/timezones.html
Step 2: Next, download the timezone archive file for your MySQL version. If you are using MySQL 5.6 and below, download the Posix standard as Windows is POSIX compliant:
Step 3: Once you have the time zones package downloaded, open a command session and navigate to the location of the time zone file:
Step 4: Finally, use the command below to load the time zone file:
mysql_tzinfo_to_sql timezone_2021a_posix_sql.zip | mysql -u root -p
Replace the time zone zip file with the name of the downloaded file.
If you complete the above command but still get an error, extract the archive above to get the timezones_posix.sql file.
Next, use MySQL to load the time zones using the command:
The above two steps should resolve the error and allow you to modify the time zones for the MySQL server.
For example, to set the time zone to New York, use the command:
Once executed successfully, you can now call the now() function to get the time based on your set time zone.
+---------------------+
| now() |
+---------------------+
| 2021-07-26 15:48:49 |
+---------------------+
NOTE: There are more options to configure MySQL time zones than the one discussed in this tutorial. Check out the documentation to learn more.
Conclusion
This guide discussed how to insert current date, current time, and current date and time into MySQL databases.
