NOW() and CURRENT_TIMESTAMP() in MySQL 8: Explained with examples

Introduction

Understanding the concept of time and date functions is crucial for database management and data analysis. MySQL, one of the most prominent database management systems, offers a range of functions to work with date and time values. Among these, the NOW() and CURRENT_TIMESTAMP() functions are widely used to fetch the current date and time. This tutorial takes an in-depth look at these functions, illustrating their use through examples.

Understanding NOW() and CURRENT_TIMESTAMP()

The NOW() function in MySQL returns the current date and time as a ‘YYYY-MM-DD HH:MM:SS’ format. It is a non-deterministic function, which means it returns a different value every time it is called, based on the current date and time.

Similarly, CURRENT_TIMESTAMP() is a temporal function that also retrieves the current date and time. In MySQL, CURRENT_TIMESTAMP() is a synonym for NOW(), and both functions can be used interchangeably.

Basic Usage of NOW() and CURRENT_TIMESTAMP()

SELECT NOW();
-- Output: '2023-04-01 12:45:30'

SELECT CURRENT_TIMESTAMP();
-- Output: '2023-04-01 12:45:30'

These functions can also be used within other SQL statements to record the date and time of data entries automatically:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  created_at DATETIME DEFAULT NOW()
);

INSERT INTO users (username) VALUES ('JohnDoe');

SELECT * FROM users;
-- Output: | 1 | JohnDoe | 2023-04-01 12:45:30 |

Here, any new record added to the users table will automatically have the current date and time recorded in the created_at field.

Advanced Usage and Examples

In more complex queries, NOW() and CURRENT_TIMESTAMP() can be used for time comparisons and calculations:

SELECT username, created_at FROM users WHERE created_at > NOW() - INTERVAL 7 DAY;
-- This will return users created in the last 7 days.

Another usage could be in automated events:

CREATE EVENT reset_weekly_data
ON SCHEDULE EVERY 1 WEEK STARTS NOW()
DO
  TRUNCATE TABLE weekly_reports;

This will create an event that truncates the weekly_reports table every week, starting from the current moment.

Functions like NOW() and CURRENT_TIMESTAMP() can also be used to set default values or as part of triggers:

DELIMITER //
CREATE TRIGGER update_last_modified BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.last_modified = NOW();
END; //
DELIMITER ;

-- Now, every time a user record is updated, the 'last_modified' field will be automatically set to the current date and time.

Conclusion

In this tutorial, we explored various aspects of NOW() and CURRENT_TIMESTAMP() in MySQL 8, demonstrating their utility and flexibility. These functions are essential tools for managing timestamp data and are instrumental for both simple and complex database operations.