Inserting Date and Time into a MySQL Table
Date and Time in MySQL – Part 10
Forward: In this part of the series I explain how date and time can be inserted into a MySQL table.
By: Chrysanthus Date Published: 5 Sep 2012
Introduction
Inserting a Date or Time
The default format for a MySQL date is, yyyy-mm-dd, e.g 2012-09-4, meaning the 4th of September 2012. To insert this date into a table you type the insertion statement in the normal way with the date as a string. The default time format is hh:mm:ss, e.g. 15:30:55. You also insert this time in the normal way with the time as a string.
Imagine the following MySQL table:
Temp(tempID, name, dateOfBirth, timeOfBirth)
where the dateOfBirth column is of the MySQL table data type, date and the timeOfBirth column is of the MySQL table data type, time. The following statement will insert a particular date and time to the table:
insert into Temp (name, dateOfBirth, timeOfBirth) values ("lionpet", "2012-09-4", "15:30:55");
To insert the current date and current time, for the above table, you replace the actual date with the CURDATE() function and the actual time with the CURTIME() function, in the insertion statement. No quotes are needed around the functions. The following example illustrates this:
insert into Temp (name, dateOfBirth, timeOfBirth) values ("catpet", CURDATE(), CURTIME());
This means that you can use a MySQL date or time function in an insert statement.in place of a date or time value. The returned value (result) of the function is the recorded value.
The timestamp
The timestamp is actually a MySQL table data type. If a table has the timestamp as data type for a column, then when a row is inserted, you will not need to include any value for the timestamp cell. The current date and time will automatically be inserted by the MySQL server into the timestamp cell. The default timestamp format is, yyyy-mm-dd hh:mm:ss . Consider the following table:
Temp(tempID, name, dateAndTimeOfBirth)
Assume that the data type for the dateAndTimeOfBirth column is timestamp. The following statement is a typical insertion statement for a row in the table:
insert into Temp (name) values ("dogpet");
In this case the value of the timestamp will automatically be inserted. Note that the insertion statement does not have any value for the timestamp. An example of a timestamp value is, "2012-09-4 15:30:55".
Note: MySQL table data types are simply referred to as MySQL data types.
End of Tutorial and End of Series
We have come to the end of the tutorial and the end of the series. If you have understood most of the principles of this series, then you are in the position to handle dates and times in MySQL, with ease.
Chrys