MySQL NOW() Function
In SQL NOW() function is used to return the system date and time. This function is very useful for recording the date and time of adding or modifying data, in a DATETIME() or a DATE(). This allows you to know exactly when a piece of data contained in a table was added.
Syntax
The NOW() function is very easy to use in SQL. Just use a query with syntax like the one below:
1 2 3 | SELECT NOW(); |
This request will return the date and time. A possible result is therefore the following:
1 2 3 | 2018-09-22 16:19:43 |
Date and time are displayed in YYYY-MM-DD format HH: MM: SS (Y = Year, M = Month, D = Day, H = Hour, M = Minute, S = Second).
Example
Add a record with today’s date
Imagine any application that has a database to store information. This database contains the table of users with their login, email and the date when the user was added. This date is very handy for finding out exactly the day the user was added. This can be useful in the case of application maintenance if a bug has been detected by a user.
User table:
| id | login | date_added | |
|---|---|---|---|
| 1 | admin | admin@example.com | 2013-04-02 12:45:34 |
| 2 | jojo | jojo@example.com | 2013-04-11 17:36:11 |
| 3 | toto | toto@example.com | 2013-09-02 09:08:47 |
Imagine adding a user on September 22, 2013. The SQL query to add this user will look like this:
1 2 3 4 | INSERT INTO utilisateur ( login, email, date_added ) VALUES ( 'new_user', 'new_user@example.com', NOW() ); |
Result:
| id | login | date_added | |
|---|---|---|---|
| 1 | admin | admin@example.com | 2013-04-02 12:45:34 |
| 2 | jojo | jojo@example.com | 2013-04-11 17:36:11 |
| 3 | toto | toto@example.com | 2013-09-02 09:08:47 |
| 4 | new_user | new_user@example.com | 2013-09-22 17:06:22 |
This result clearly shows that the date and time will be added, without having to determine them via a programming language. It is therefore possible to keep an SQL query simple enough to add users.
MySQL Return Current Date
It is easy to get the current date from the NOW() function. To do this, just use the DATE() or CAST() function.
Extract the date with the DATE() function:
The DATE() function is used to obtain a date from a character string in DATE or DATETIME format. Just use the following syntax to get today’s date.
1 2 3 | SELECT DATE (NOW()); |
Result:
1 2 3 | 2013-09-22 |
MySQL Extract the date with the CAST() function:
The CAST() function converts one type to another type. It is therefore possible to convert the present moment in DATETIME format into a DATE type result.
1 2 3 | SELECT CAST(NOW() AS DATE); |
1 2 3 | 2013-09-22 |
