MySQL

How to get current date & time in MySQL2 min read

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:

This request will return the date and time. A possible result is therefore the following:




 

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 email 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:

Result:

id login email 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.

 

Result:

 

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.

Result:

Leave a Comment