Automatically insert Current Date and Time in MySQL table #Part – 2.2

Previously we have discussed widely on the Date and Time datatypes of MySQL. Date and Time both are important to keep exact record of inserted data in a particular table. So, the usage of Date/Time is wide in MySQL and PHP. Today we shall see how we can automatically insert current Date and Time using MySQL queries.

Basically current date and time can be inserted into a properly defined MySQL table using 3 simple functions… CURDATE(), CURTIME(), NOW(). With the proper usage of these functions you can easily play with date and time in MySQL. Below we have elaborated how to use them and where to use them…

Creating a table for the demonstration:

For the ease of this discussion, we are first going to make a MySQL table in the following manner.

mySQL function	dateTime	date	time	year	timeStamp
…	…	…	…	…	…

Obviously DATETIME, DATE, TIME etc are set to its relevant Date and Time datatypes. Now to make this table we open up the MySQL terminal and enter the following command:

Click on the picture to enlarge

Note that here we have used a custom database date_time to store this table.

CURDATE() MySQL function to automatically insert date:

This command is used to insert current date (with respect to the execution of this command) into a MySQL table. It can be applied on either of the Four (4) MySQL Datatypes => DATE, DATETIME, YEAR & TIMESTAMP. But in all the cases only the date will be recorded on the field. Rest data (if present) will be stored as its Zero Value (Zero value has been discussed on the previous article). On the YEAR field, only Year value will be snatched from the date.

Format:

YYYY-MM-DD

Demonstration:

Simply run the following two sets of commands on your MySQL terminal or Command Prompt Window…

The Following result will show up:

Click on the picture to enlarge

CURTIME() MySQL function to automatically insert Time:

CURTIME() is used to automatically insert time on MySQL table field. Unlike CURDATE()  it can be used only with TIME datatype fields. Obviously on errors, the field will return its Zero Value.

Format:

HH:mm:SS

Demonstration:

Run the following code on your MySQL terminal. The result will clear your concept about the usage of CURTIME().

Click on the picture to enlarge

Note the error crept in the YEAR field. So be careful while using this function with these types of fields.

NOW() MySQL function to automatically insert Date and Time:

NOW() is used to insert the current date and time in the MySQL table. All Fields with datatypes DATETIME, DATE, TIME & TIMESTAMP works good with this function.

YYYY-MM-DD HH:mm:SS

Demonstration:

Following code shows the usage of NOW()

This will result like:-

Click on the picture to enlarge

Overview on the MySQL Table:

This will be MySQL auto_ins table we have made through this tutorial:

mySQL function	dateTime	date	time	year	timeStamp
CURDATE()	2010-02-02 00:00:00	2010-02-02	00:00:00	2010	2010-02-02 00:00:00
CURTIME()	0000-00-00 00:00:00	0000-00-00	23:09:53	2023	0000-00-00 00:00:00
NOW()	2010-02-02 23:34:48	2010-02-02	23:34:48	2010	2010-02-02 23:34:48

 

So from this table we can conclude the following usage on the MySQL date time functions:

MySQL date Time functions	usable field datatype	remark
CURDATE()	DATE, DATETIME, TIMESTAMP, YEAR	Can store only the date value on DATETIME and TIMESTAMP. The Time will be set to the ZERO VALUE
CURTIME()	TIME	Returns error while used in fields with datatype YEAR
NOW()	DATE, TIME, DATETIME, TIMESTAMP, YEAR	Can be used to all date/time datatype fields. It is synonymous to LOCALTIMESTAMP() LOCALTIME()CURRENT_TIMESTAMP()etc

---

That was the fundamentals of MySQL date/time functions. But there are more. You can read about them here at the official MySQL documentaries. I hope you have enjoyed this article. Do give you feedback. Also we love questions… So if you have one, throw it through your comment.