0

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).

Community
  • 1
  • 1
Trees4theForest
  • 1,267
  • 2
  • 18
  • 48
  • Store your time as seconds since the epoch, and ensure that all consumers of the database do the same as well. Keep in mind that seconds since epoch is _independent_ of timezone. – Tim Biegeleisen Jul 19 '16 at 05:10
  • 1
    I shoot for `utc_timestamp()` – Drew Jul 19 '16 at 05:12
  • The trick being, if I store as timestamp (which MySQL converts to local time on viewing) then a user in California will see a different time from what was recorded on the photo (in Africa) – Trees4theForest Jul 19 '16 at 05:16
  • Storing as UTC timedate (not timestamp) then means every MySQL view / further processing must take this into consideration, and re-convert to Arfica time -- while it would be simpler to leave it as Africa time through and through. – Trees4theForest Jul 19 '16 at 05:17
  • I don't see what the issue is. And varchar iis out of the question – Drew Jul 19 '16 at 05:25
  • @drew Yeah, I'm not using VARCHAR, don't worry ;) -- In a nutshell, I want the user to be able to look at the "Date and Time" columns in the DB and have them match exactly the Date and Time printed on the photo - no matter where they are -- and without having to convert a UTC time stamp back to the timezone the photo was taken (which will never change) – Trees4theForest Jul 19 '16 at 05:27
  • If you can suspend in reality for a moment that the picture has the timestamp printed on it as UTC, what is the issue – Drew Jul 19 '16 at 05:29
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/117656/discussion-between-trees4theforest-and-drew). – Trees4theForest Jul 19 '16 at 05:41

3 Answers3

1

Since the server will convert all timestamps to server time and you need the local time, it seems necessary to store the timezone in a separate column.

I'd recommend storing times in UTC and the timezone as an integer +/- UTC in minutes. While this will require extra processing, it allows for the data you need to be stored in a fairly consistent matter.

Note that you should not store the timezone as a VARCHAR(32) e.g. Asia/Riyadh. This is because doing so would not take into account daylight savings so your times could be off by an hour.

areke
  • 126
  • 1
  • 8
0

mysql get server time stamp not client time stamp if you need to store data time depends on client time stamp you need to send data in your query

tapos ghosh
  • 2,114
  • 23
  • 37
0

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).

Trees4theForest
  • 1,267
  • 2
  • 18
  • 48