Solomon Closson Solomon Closson - 1 year ago 120
PHP Question


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

strings into a
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
now since I have it on a column of the database, since it is only capable of being used on
types. Would this use a timestamp that is not in the format:
when it updates based on

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
ability to avoid a problem with mixed data coming from the database here?

Answer Source

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.