CODEWITHSUNDEEP
mysqldatetime
AL̲̳I
AL̲̳I

Reputation: 2461

Should I use timestamp or datetime for sensor data

I know this is common question and has been answered on this forum but my question is specific to a scenario. I am storing sensor data in a database table

CREATE TABLE `sensordata` (
  `userID` varchar(45) NOT NULL DEFAULT '',
  `instrumentID` varchar(10) NOT NULL DEFAULT '',
  `utcDateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `localDateTime` datetime DEFAULT NULL,
  `data` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`userID`,`instrumentID`,`utcDateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

localDateTime column is redundant so I want to get rid of it but I have to keep track of the local time also because we allow users to change the timezone of a sensor that is why I keep the local time of the recorded data. I also want to change the datatype of utcDateTime to timestamp so that I can perform time conversion in the SELECT query. For example, 

If a sensor is recording in Sydney, one day the user changes its timezone to UTC, how will I know which data is for what time (if I use the current timezone of the sensor).

Should I stick with this schema or should I create another table to track the timezone changes and parse the date according to that ? Please advice, if there is a better solution. Thanks

Upvotes: 1

Views: 477

Answers (2)

user170442
user170442

Reputation:

I would use DATETIME to store UTC+0 time along with localTimeOffset (in minutes) at the time of sensor reading. Why?

There is no simple logic how offset changes over time - it might be different for different countries over time, also DST changes are applied differently. Some countries make DST adjustment in their timezone, some countries (I think Australia is one of them) change timezone.

Having utcDatetime and localTimeOffset will let you reproduce exact time of event under any circumstances, including change of time zone for sensor and will let you to convert event time into user's timezone time.

Side note: you have to store localTimeOffset in minutes as there are countries which have offsets like UTC+05:45 (Nepal)

Upvotes: 2

Feralheart
Feralheart

Reputation: 1940

My two cents is to use a "servertimestamp" and a "localtimestamp", so you can see which timestamp is which. And next to it I would make an "eventlog" to see which user when changed his/her configurations.

Upvotes: 1

Related Questions