Left on the Web

MySQL Weirdness

I'm currently working with extending an existing application, so I'm working with a database someone else designed. Not a problem usually, except for the fact that this person chose to use the TIMESTAMP fieldtype, which I usually don't use. Now, when I think of a timestamp, I automatically think of a Unix Timestamp, which is the number of seconds since 01/01/1970. This is what most apps use. Not MySQL, as I found out. While trying to format the timestamp with the php date function, I got some weird data, so I checked the MySQL documentation. Turns out that for some reason beyond my understanding, MySQL does not use Unix Timestamps, but instead uses a string similar to the Datetime field, but without the special characters. So, for instance, august 1st 2005, 12:00 would be: 20050801120000. I just can't find any reason for MySQL doing this, but they did it. Of course, after I found this out, I am now formatting the timestamp field differently

I'm currently working with extending an existing application, so I'm working with a database someone else designed. Not a problem usually, except for the fact that this person chose to use the TIMESTAMP fieldtype, which I usually don't use. Now, when I think of a timestamp, I automatically think of a Unix Timestamp, which is the number of seconds since 01/01/1970. This is what most apps use. Not MySQL, as I found out. While trying to format the timestamp with the php date function, I got some weird data, so I checked the MySQL documentation. Turns out that for some reason beyond my understanding, MySQL does not use Unix Timestamps, but instead uses a string similar to the Datetime field, but without the special characters. So, for instance, august 1st 2005, 12:00 would be: 20050801120000. I just can't find any reason for MySQL doing this, but they did it. Of course, after I found this out, I am now formatting the timestamp field differently
August 1, 2005 - tags: left on the web