3

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?

Solomon Closson
  • 6,111
  • 14
  • 73
  • 115

1 Answers1

4

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.

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • Well, right now I changed some columns to TIMESTAMP, but Mysql still shows them as a string in the format: `YYYY-MM-DD HH:MM:SS` so nothing was converted. – Solomon Closson Sep 17 '16 at 22:09
  • It has been converted. Trust me :) It's just your client shows this in human friendly format. – Mchl Sep 17 '16 at 22:10
  • Using phpMyAdmin, and it shows them as `YYYY-MM-DD HH:MM:SS` strings still. Also, what is the best way to get these from the database via php, do I still need to convert to time, using `strtotime` function? – Solomon Closson Sep 17 '16 at 22:11
  • Yes. You will get the exact same format, as you would get from DATETIME field, so `strtotime` will work just the same. – Mchl Sep 17 '16 at 22:13
  • So, when it updates it will be a number format, and I still use `strtotime` on the number format? – Solomon Closson Sep 17 '16 at 22:14
  • 1
    Awwww, your updated answer helped a lot. I was thinking that TIMESTAMP actually means a timestamp, as in php `timestamp`, but I think I understand it now. Thanks many! – Solomon Closson Sep 17 '16 at 22:18
  • 1
    It is stored as numeric value internally, but you will never see this numeric value, unless you use `UNIX_TIMESTAMP` function. All queries will by default return string with formatted dates. – Mchl Sep 17 '16 at 22:18
  • 1
    Excellent. I'll just reiterate once again: make sure you set correct timezone before you import your date. Otherwise you might be in for a surprise, when all your data suddenly is moved several hours ahead or behind. https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html – Mchl Sep 17 '16 at 22:23