Introduction to Date and Time Functions in MySQL

Date and time functions are essential for working with temporal data in MySQL. They allow you to manipulate, extract, and format date and time values within your SQL queries. In this guide, we'll explore two commonly used date and time functions in MySQL: NOW() and DATE().

The NOW() Function

The NOW() function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'. It is often used to record the timestamp of when a record is created or modified in a database.

Basic usage of the NOW() function:

            SELECT NOW();
        

This query returns the current date and time.

The DATE() Function

The DATE() function extracts the date part from a date and time expression, leaving only the 'YYYY-MM-DD' portion. It is useful when you want to work with date values independently.

Basic usage of the DATE() function:

            SELECT DATE(NOW());
        

This query extracts and returns the current date.

Example of Using Date and Time Functions

Let's consider an example of using these functions to record the creation date and time of a new user:

            INSERT INTO users (username, created_at)
            VALUES ('newuser', NOW());
        

In this example, the NOW() function is used to capture the current date and time when a new user is created.

Conclusion

MySQL's date and time functions like NOW() and DATE() are invaluable for working with temporal data in your database. They allow you to capture, manipulate, and format date and time values, providing essential capabilities for various applications, from recording events to generating reports.