MySQL MariaDB

How to Insert Current Date and Time in MySQL

None of us can dispute that time is a critical factor both in computing and the real world. It allows us to create records of when an action takes place.

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.

2021-07-26 22:08:15

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:

select sysdate();
+---------------------+
| 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:

SELECT curdate();
+------------+
| 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:

SELECT curtime();
+-----------+
| 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:

SET time_zone =time zone’;

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:

cd C:\users\linuxhint\Desktop

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:

mysql -u root -p -D mysql < timezone_posix.sql

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:

SET time_zone = 'America/New_York';

Once executed successfully, you can now call the now() function to get the time based on your set time zone.

select now();
+---------------------+
| 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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list