Question:

How do I add a date and/or time into a MySQL table?

Answer:

There are 3 field types in MySQL that specificially deal with dates and times. They are DATE, DATETIME and TIMESTAMP. DATE only stores the date whereas DATETIME and TIMESTAMP also store a time as well. TIMESTAMP is referenced with UNIX epoch time and can only store dates from 1970 until 2037. If you require dates outside this range, then you should use DATETIME.

DATETIME columns store their values on the database table as 'YYYY-MM-DD HH:MM:SS'. DATE columns only store the date part, and TIMESTAMP generally stores data as 'YYYYMMDDHHMMSS', however this is dependent on the MySQL server that is running and the configuration settings.

Dates can be inserted with any of the following formats (the first two formats can be either a string or number).

YYYYMMDD
YYYYMMDDHHMMSS
'YYYY-MM-DD'
'YYYY-MM-DD HH:MM:SS'

Example:

$sql[0] = "INSERT INTO public_holidays values (0, 'Christmas Day', 20061225);

$sql[1] = "INSERT INTO public_holidays values (0, 'New Years', '2006-01-01 00:00:00');

$sql[2] = "INSERT INTO public_holidays values(0, 'EKKA', '2006-08-06');

Additional Notes:

You can still supply a time value when inserting into a DATE column however only the date part will be saved in the database column.

MySQL

How do you display the contents of a table on the browser?
Learn how to connect to a database, write and execute a query and display the results on the browser

Comments or questions relating to this article have been disabled. They will be back soon.