mysql - 我应该为传感器数据使用时间戳还是日期时间

Question

我知道这是一个常见问题，并且已在本论坛上得到解答，但我的问题是特定于某个场景的。我将传感器数据存储在数据库表中

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 列是多余的，所以我想去掉它，但我还必须跟踪本地时间，因为我们允许用户更改传感器的时区，这就是我保留记录数据的本地时间。我还想将 utcDateTime 的数据类型更改为 timestamp 以便我可以在 SELECT 查询中执行时间转换。例如，

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

我应该坚持这个模式还是应该创建另一个表来跟踪时区变化并根据它解析日期？请指教，如果有更好的解决方案。谢谢

Answer 1

我会使用 DATETIME 来存储 UTC+0 时间以及传感器读取时的 localTimeOffset(以分钟为单位)。为什么？

偏移量如何随时间变化没有简单的逻辑 - 随着时间的推移，不同的国家可能会有所不同，DST 变化的应用也不同。一些国家在他们的时区进行夏令时调整，一些国家(我认为澳大利亚是其中之一)改变时区。

拥有 utcDatetime 和 localTimeOffset 可以让您在任何情况下重现准确的事件时间，包括传感器时区的更改，并且可以让您将事件时间转换为用户的时区时间。

旁注:您必须以分钟为单位存储 localTimeOffset，因为有些国家/地区具有像 UTC+05:45(尼泊尔)这样的时差