SQlite：unix时间戳的列格式；整数类型
原来的问题： unix 时间戳的正确列格式是什么？
网络上充满了混乱：一些帖子声称 SQLite 没有无符号类型 - 不管怎样，或者除了 64 位 int 类型（但有调用 UNSIGNED INTEGER 的（反）示例）。数据类型页面仅在 bigint 示例中提到它。它还声称有一个 6 字节整数，但没有给出它的名称。看来我尝试使用 INTEGER 作为 4 字节签名将 unix 时间戳存储为负数。我听说有些系统也返回 64 位时间戳。 OTOH 我不太喜欢浪费 4 个字节来存储 1 个额外位（时间戳的最高位），即使我必须选择一种更大的数据格式，我也宁愿选择 6 字节格式。我什至看到一篇文章声称 SQLite unix 时间戳是 REAL 类型...
完整的问题： 有人可以澄清一下这个混乱吗？
Original problem:
What is the right column format for a unix timestamp?
The net is full of confusion: some posts claim SQLite has no unsigned types - either whatsoever, or with exception of the 64bit int type (but there are (counter-)examples that invoke UNSIGNED INTEGER). The data types page mentions it only in a bigint example. It also claims there is a 6-byte integer but doesn't give a name for it. It seems my tries with INTEGER being 4-byte signed signed store unix timestamps as negative numbers. I've heard that some systems return 64-bit timestamps too. OTOH I'm not too fond of wasting 4 bytes to store 1 extra bit (top bit of timestamp), and even if I have to pick a bigger data format, I'd rather go for the 6-byte one. I've even seen a post that claims SQLite unix timestamp is of type REAL...
Complete problem:
Could someone please clarify that mess?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
整数的大小
SQLite 数据库中的所有列在内部都是可变宽度的。 文件格式以 1、2、3、4、6 或 8 字节存储整数，根据数字有多大，在头部加一个字节来表示大小。因此，总的来说，存储为整数的 Unix 日期在 2038 年 1 月 19 日之前将占用 5 个字节，之后将占用 7 个字节。
从 C API 用户的角度来看，所有整数都是有符号的 64 位。
列类型
无论您将列声明为 INTEGER、UNSIGNED INTEGER、BIGINT 还是其他类型，都没有关系。 任何带有“INT”的东西都具有整数关联性。并且，如上所述，所有整数都是有符号的64 位，但通常不以这种方式存储。
The size of an integer
All columns in SQLite databases are internally variable-width. The file format stores integers in 1, 2, 3, 4, 6, or 8 bytes, depending on how big the number is, plus one byte in the header to indicate the size. So, in total, Unix dates stored as integers will take up 5 bytes until 2038-01-19 and 7 bytes after that.
From the point of view of the user of the C API, all integers are signed 64-bit.
The column type
It doesn't matter whether you declare your column as INTEGER, UNSIGNED INTEGER, BIGINT, or whatever. Anything with "INT" in it has integer affinity. And, as mentioned above, all integers are signed 64-bit but not usually stored that way.
SQLite 没有无符号类型。这是直接来自主要作者以及文档。此外，它没有固定的整数列宽；实际的磁盘宽度是一个实现细节。
SQLite 没有日期或时间数据类型。但是，它具有可以对 ISO8601 字符串 (TEXT)、儒略日数字 (REAL) 和 Unix 时间戳 (INTEGER) 进行操作的日期函数。
因此，如果您决定将时间字段设置为 Unix 时间戳，请注意它可以存储最多 64 位有符号整数，但您现在存储的值实际上应该占用磁盘上的 32 位，即使源值是 64 位
time_t。SQLite does not have unsigned types. That's directly from the main author, as well as the docs. Moreover, it doesn't have fixed column widths for integers; the actual on-disk width is an implementation detail.
SQLite has no date or time datatype. However, it has date functions that can operate on ISO8601 strings (TEXT), Julian day numbers (REAL), and Unix timestamps (INTEGER).
So if you decide to make your time field a Unix timestamp, know that it can store up to 64-bit signed integers, but values you store now should actually occupy 32 bits on disk, even if the source value is a 64-bit
time_t.我更喜欢 64 位整数。无符号 32 位整数的经典情况是自 1970 年 1 月 1 日以来的秒数在 2038 年耗尽。请参阅 http://en.wikipedia.org/wiki/Unix_time 和 http://en .wikipedia.org/wiki/Year_2038_problem。使用 64 位无符号整数，您是安全的
My preference would be for a 64-bit integer. The classic case of an unsigned 32-bit integer is with seconds since 1970-01-01 runs out in 2038. See http://en.wikipedia.org/wiki/Unix_time and http://en.wikipedia.org/wiki/Year_2038_problem . With a 64-bit unsigned integer, you're safe
您能否举个例子来说明“我似乎尝试使用 INTEGER 将 4 字节签名的 UNIX 时间戳存储为负数”？
如果您还没有阅读过，我建议您阅读有关 数据类型 的 SQLite 文档（第 1.2 节“日期和数据”）时间数据类型）和日期和时间函数。
Could you give an example of what you mean by "It seems my tries with INTEGER being 4-byte signed signed store unix timestamps as negative numbers."?
If you haven't already I'd suggest reading SQLite docs on datatypes (section 1.2 Date and Time Datatype) and date and time functions.
如果您使用的是内存情况至关重要的嵌入式系统，则可以考虑通过将 64 位值移动几位来降低精度（导致精度为 2、4、8... 秒，而不是 1 秒），并且使用 32 位值来存储它。
If you're on an embedded system where the memory situation is critical, you can consider dropping precision by shifting the 64-bit value several bits (resulting in a precision of 2, 4, 8... seconds instead of 1 sec) and using a 32-bit value to store it.