Trees4theForest Trees4theForest - 4 months ago 17
MySQL Question

MySQL Storing Datetime in Local Time forever and always

I'm storing photo time-stamps in a MySQL database. Currently, I have my column set to:

CREATE TABLE `photos` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`camname` varchar(45) DEFAULT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


Date and Time represents the local time that the picture was taken as evidenced by the timestamp on printed on the image. Therefore 10:45am will always, and should always be 10:45 am, no matter what timezone I retrieve or manipulate that data.

This is vital for analysis -- we're looking at the time of day events occur. And how many days elapsed, etc. Knowing when it happened int relation to the localtime later on is irrelevant, and useless.

So, what is the best way to store datetime data in a MySQL database so it displays always in the time it was initially recorded?




Related: Should I use field 'datetime' or 'timestamp'?

Which I think has the answer: With DATETIME, what you input is what you get. 2015-01-23 12:45:34 will ALWAYS be 2015-01-23 12:45:34 while TIMESTAMP will display differently on local cleints, DATETIME will not.

However, taking the advice of some of the comments, it seems a good idea to include the timezone so eons later someone can know what timezone that DATETIME refers to. (If they should care).

Answer

With DATETIME, what you input is what you get: 2015-01-23 12:45:34 will ALWAYS be 2015-01-23 12:45:34

With TIMESTAMP MySQL records as seconds since the epoch (point in time) and will display differently on local clients

However, taking the advice of some of the comments, it seems a good idea to include the timezone so eons later someone can know what timezone that DATETIME refers to. (If they should care).