Are stored in UTC
They are converted to UTC on storage and converted back to your time zone on retrieval. If you change time zone settings, the retrieved values also change.
Can be automatically initialised and updated
You can set their default value and / or auto update value to
Have a range of
1970-01-01 00:00:01 UTCto
2038-01-19 03:14:07 UTC
Whereas MySQL datetime:
What you store is what you get ™.
Have a range of
Values outside the range may work - but only values within the range are guaranteed to work.
You can store dates where day or month is zero.
This is the MySQL way of storing birthdays! You can't do that with a
TIMESTAMP, with the only exception being the zero value of
You can store invalid dates, if you need them, with in ALLOW_INVALID_DATES mode.
You can set default value to
NOW()in some instances, but the preferable and more natural way of automatically initialised and updated dates is
And of course there are also
TIME, which work just like
DATETIME, but of course
DATE doesn't care for time and
TIME doesn't care for date. All four data types work perfectly with the wide array of date and time functions, but when you are mixing data types you should be aware of how conversion works.
Now, to your question: You should use
DATETIME everywhere. Not for technical reasons, but since you are still not very clear on how MySQL works,
DATETIME is the simpler choice. This will mean that you will have to calculate the current timestamp in PHP before storing, that's as easy as:
$mysqldate = date("Y-m-d H:i:s");
PHP's date function works like this:
string date ( string $format [, int $timestamp = time() ] )
"Y-m-d H:i:s" format is the one compatible with MySQL and by leaving the second parameter empty,
time() to get the current UNIX timestamp.
TIMESTAMP instead of a
DATETIME has the added value of MySQL taking over the responsibility of deciding on the current timestamp, and you can skip the field when you are inserting / updating. But since you are already sending a query, and the code to get the current timestamp in PHP is minimal, you can safely go with
DATETIME for everything.
As for the actual code to store the PHP timestamp into the database, you should look at PHP Data Objects, and if you are still unclear, ask on StackOverflow instead. But there are almost 5k related questions already, make sure you go through them before asking. Just a hint, prepared statements is how the cool kids do it.