Solomon Closson Solomon Closson - 2 months ago 18
PHP Question

TIMESTAMP vs. DATETIME MySQL

I needed to convert a database from SQL to MySQL and did so successfully, however, this question is more geared towards MySQL now.

I noticed that I'm able to insert

DateTime
strings into a
Timestamp
column type, and that's all fine and good, from what I hear about Timestamp accepting either DateTime or actual Timestamps, however, I'm curious about the
"ON UPDATE CURRENT_TIMESTAMP"
now since I have it on a column of the database, since it is only capable of being used on
Timestamp
types. Would this use a timestamp that is not in the format:
YYYY-MM-DD HH:MM:SS
when it updates based on
CURRENT_TIMESTAMP
?

How would this influence the data that I pull from this column exactly?

For Example, would it be difficult to get the current date/time from the TIMESTAMP column when some dates might be a numeric timestamp, while others might be in DATETIME format? Should I just use DATETIME as the type for this column without the
ON_UPDATE
ability to avoid a problem with mixed data coming from the database here?

Answer

Internally all TIMESTAMP data is stored as 4 byte integer denoting number of seconds since Unix epoch. What you see presented in your software is dependent on how your software presents this data. For example, if you change your timezone to different one, it will have an effect on data read from TIMESTAMP column (beacuse Unix epoch is in UTC)

There will be no mixing of data. MySQL will take care of converting your YYYY-MM-DD HH:MM:SS into timestamps. Be wary of timezones though!

When querying TIMESTAMP columns, MySQL will by default format them in human readable form, so don't expect to get integers from these. You will get a string formatted in YYYY-MM-DD HH:MM:SS just like from DATETIME fields.

Comments