使用unix纪元存储日期和日期时间
我需要一种通用的方法来在多个数据库（目前是 Sqlite、MySql 和 PostgreSql）中存储日期和日期时间。 我需要日期时间具有微秒精度。
- Sqlite 没有内置日期和日期时间。MySql
- 日期时间没有微秒精度。
所以我想将日期保留为整数（4字节） - 自unix纪元以来的天数，并将日期时间保留为整数（8字节） - 自unix纪元以来的微秒。
问题：
- 将日期转换为自 UNIX 纪元以来的天数的正确方法是什么？我发现了这个：
time.time()/86400(python)。 - 将日期时间保留为时间戳是否可靠？闰秒怎么样——这会影响准确性吗？如果在某个时间点将未来日期存储为时间戳但后来出现闰秒怎么办？
- 还有其他问题吗？
I need a universal way to store dates and datetimes in several databases (for now Sqlite, MySql and PostgreSql).
I need datetimes to have microseconds precision.
- Sqlite does not have date and datimes built in.
- MySql datetime does not have microseconds precision.
So i thought to keep dates as integers (4 bytes) - days since the unix epoch, and datetimes as integers (8 bytes) - microseconds since the unix epoch.
Questions:
- What's the right way to convert a date to days since unix epoch? I found this:
time.time()/86400(python). - Is it robust to keep datetimes as timestamps? What about the leap seconds - will this influence the accuracy? What if at some point in time a future date was stored as timestamp but later a leap second appeared?
- Any other issues?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
time() / 86400 就可以了。我以前做过这个；您不应该预料到任何问题。您还可以存储截断为最接近的 86400 倍数的 time() ，这可能会好一点，因为您可以将它传递给已经接受
time_t的各种函数，并将其显示为午夜 UTC在相关日期。不用担心闰秒。只有专门的软件才会考虑它们。所有通用操作系统上的系统日期和时间以及所有通用日期和时间库都假装它们不存在。您的 NTP 服务器通过在它们发生时将系统时钟提前一秒来实现它们。
C 的
gettimeofday将时间返回为两个单独的 4 字节整数值：自纪元以来的秒数和自秒开始以来的微秒数 (struct timeval)。类似地，C 的clock_gettime将时间返回为两个单独的 4 字节整数值：自纪元以来的秒数和自第二秒开始以来的纳秒 (struct timespec)。如果您关心与现有时间表示形式的兼容性，您可以选择这两种格式之一，而不是自纪元以来计数微秒的 8 字节整数。另一方面，这两个都存在 y2.038k 错误（除非第一个整数扩展到 8 个字节，总共 12 个字节），并且它们在数据库中使用起来不太方便。因此，我认为您对纪元以来微秒的想法很好。time() / 86400 is fine. I've done this before; you should not anticipate any problems. You could also store time() truncated down to the nearest multiple of 86400, which might be a little nicer because you can pass it to various functions that already accept a
time_tand have it come out as midnight UTC on the date in question.Don't worry about leap seconds. Only specialized software takes them into account. The system date&time on all general purpose operating systems, and all general purpose date&time libraries pretend they don't exist. Your NTP server implements them by just fudging the system clock ahead by one second whenever they happen.
C's
gettimeofdayreturns the time as two separate 4-byte integer values: seconds since the epoch, and microseconds since the start of the second (struct timeval). Similarily, C'sclock_gettimereturns the time as two separate 4-byte integer values: seconds since the epoch, and nanoseconds since the start of the second (struct timespec). If you care about compatibility with existing representations of time, you might choose one of those two formats instead of an 8-byte integer counting microseconds since the epoch. On the other hand both of those have the y2.038k bug (unless the first integer is extended to 8 bytes, for a total of 12 bytes) and they are less convenient to use in a database. So I think your idea of microseconds since the epoch is just fine.