Database Reference
In-Depth Information
Current Date and Time
The most basicdate and time functions are those related to the current date and time. They
may be used for recording the current date and time in a column, for modifying results
based on the current date and time, or for displaying the date and time in a results set. Let's
start with the simplest one,
NOW()
, which determines what time it is when you execute the
statement. Enter the first line shown here in
mysql
(an example of the results follow):
SELECT NOW( );
+---------------------+
| NOW( ) |
+---------------------+
| 2014-02-08 09:43:09 |
+---------------------+
As you can see, that returns the date and time on a server in a format that matches the
format of the
DATETIME
data typeSo if you have a column in a table that uses that data
type, you can usethe
NOW()
function to conveniently insert the current date and time into
the column. The
bird_sightings
table has a column that uses the
DATETIME
data
type, the
time_seen
column. Here's an example of how we might enter a row into that
table using
NOW()
:
INSERT INTO
bird_sightings
(
bird_id
,
human_id
,
time_seen
,
location_gps
)
VALUES
(
104
,
34
,
NOW
( ),
'47.318875; 8.580119'
);
This function can also be used with an application, or with a script for a web interface so
that the user can record bird sightings without having to enter the time information.
NOTE
There are a few synonyms for the
NOW()
function:
CURRENT_TIMESTAMP()
,
LOCALTIME()
, and
LOCALTIMESTAMP()
. They return the exactsame results. Synonyms such as these are provided so that
MySQL and MariaDB will conform to functions in other SQL database systems. This way, if you have an
application that uses another database (e.g., PostgreSQL, Sybase, Oracle), you can more easily replace it
with MySQL without having to change the code in your applications.
The
NOW()
function returns the date and time at the start of the SQL statement containing
it. For most purposes, this is fine: the difference between the time at the start and at the
completion of an SQL statement is usually minimal and irrelevant. But you may have a
situation in which an SQL statement takes a long time to execute, and you want to record
