MySQL 存储持续时间 - 数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4498513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL storing duration time - datatype?
提问by Juds
I need to calculate the time a user spends on site. It is difference between logout time and login time to give me something like "Mr X spent 4 hours and 43 minutes online". So to store the4 hours and 43 minutes i declared it like this:
durationtime NOT NULL
我需要计算用户在网站上花费的时间。注销时间和登录时间之间的区别是给我类似“X 先生在网上花了 4 小时 43 分钟”这样的信息。所以为了存储 4 小时 43 分钟，我是这样声明的：
durationtime NOT NULL
Is this valid or a better way to store this? I need to store in the DB because I have other calculations I need to use this for + other use cases.
这是有效的还是更好的存储方式？我需要存储在数据库中，因为我还有其他计算需要将其用于 + 其他用例。
回答by Riedsio
Storing it as an integer number of seconds will be the best way to go.
将它存储为整数秒将是最好的方法。
- The
UPDATEwill be clean and simple - i.e.duration = duration + $increment - As Tristram noted, there are limitationsto using the
TIMEfield - e.g. "TIMEvalues may range from'-838:59:59'to'838:59:59'" - The days/hours/minutes/seconds display formatting won't be hardcoded.
- The execution of your other calculationswill almost surely be clearer when working with an integer "number of seconds" field.
- 该
UPDATE会是干净和简单-即duration = duration + $increment - 正如 Tristram 所指出的，使用该字段是有限制的
TIME——例如“TIME值的范围可以从'-838:59:59'到'838:59:59'” - 天/小时/分/秒显示格式不会被硬编码。
- 使用整数“秒数”字段时，其他计算的执行几乎肯定会更清晰。
回答by Tristram Gr?bener
I wouldn't use time as you would be limited to 24 hours. The easiest would just to store an integer in minutes (or seconds depending on the resolution you need).
我不会使用时间，因为您将被限制为 24 小时。最简单的方法就是以分钟为单位（或以秒为单位，具体取决于您需要的分辨率）存储一个整数。
回答by druid62
Consider storing both values as a UNIX-epoch-delta.
考虑将这两个值存储为 UNIX-epoch-delta。
I generally prefer to use a signed (64b) bigint (for secondly resolution), or a (signed) (64b) double (if fractional seconds are needed), or a signed (32b) int (if scaled down to minutely or hourly).
我通常更喜欢使用有符号 (64b) bigint（用于二次解析），或（有符号）（64b）双精度（如果需要小数秒），或有符号 (32b) int（如果缩小到每分钟或每小时） .
Make the unit explicit in the name of the column, for example with a suffix like "_epoch_minutely", for example: "started_epoch_minutely", "finished_epoch_minutely".
在列的名称中明确单位，例如使用像“_epoch_minutely”这样的后缀，例如：“started_epoch_minutely”、“finished_epoch_minutely”。
