便携式通用时间戳格式，不受2038限制 [英] Portable general timestamp format, not 2038-limited
问题描述
我需要在数据库中存储时间戳。很简单
你可能会想，但找到一个合适的通用格式并不容易。
细节是
1）亚秒级分辨率 - 毫秒，或者更好，更详细
2）不受Unix时间戳的限制2038限制
3）可读的Java
4）可写在Perl中可写，这似乎意味着64位值
出来了/>
5）可读写的Python
6）可存储在免费数据库中 - Postgresql / MySQL
两种格式提供的格式数据库系统是8字节或
12字节值，即使我可以让Perl工作，我猜它
会很混乱。保持32位值应该给我便携性
并且很容易使用而不会模糊程序逻辑。
因为32位微秒小于50天我必须存储两个
32位值。如何拆分它们？我现在支持的选项是
来分割天和这样的部分日子：
a）存储，作为32位数字，自那以后的几天虚拟年零（在共同时代时间内没有年零点< http://en.wikipedia.org/wiki/
Common_Era>）。这允许超过五百万年的正负。
仍然不完全一般，我知道。
b）将部分天数存储为另一个32位值。它的范围将是
到86401秒 - 闰日的秒数。这个
有关更好的方法的任何想法吗？ （请回复所有。谢谢）。
-
詹姆斯
On Fri，2007-06-22 at 13：33 -0700，James Harris写道：
我要求存储时间戳一个数据库。很简单
你可能会想，但找到一个合适的通用格式并不容易。
细节是
1）亚秒级分辨率 - 毫秒，或者更好，更详细
2）不受Unix时间戳的限制2038限制
3）可读的Java
4）可写在Perl中可写，这似乎意味着64位值
出来了/>
5）可读写的Python
6）可存储在免费数据库中 - Postgresql / MySQL
PostgreSQL时间戳看起来不受Y2K38的限制：
pgtest =创建表格dt（时间戳）;
创建表格
pgtest =插入dt（a）值（''2099-01-01 01：23：45.678901''）;
INSERT 0 1
pgtest = select * from dt ;
a
----------------------------
2099-01-01 01：23：45.678901
（1排）
HTH，
-
Carsten Haese
http：//informixdb.sourceforge。 net
James Harris写道：
a）存储，作为32位数字，自虚拟年零开始的几天（在共同的时代有
没有零年
< http：//en.wikipedia。 org / wiki / Common_Era>）。
但根据同一篇文章：
（它[零年]是但是，用于天文系统和ISO 8601.）
-
Lew
6月22日上午10:33，James Harris< james.harri ... @ googlemail.com>
写道：
我需要在数据库中存储时间戳。很简单
你可能会想但找到一个合适的通用格式并不容易。
...
关于更好的方法的任何想法这个？ （请回复所有。谢谢）。
-
詹姆斯
我的规则像这样的情况下的拇指是当有疑问时将其存储为
text。一种格式我很确定我们仍然可以在2039年处理
。
- Roger
I have a requirement to store timestamps in a database. Simple enough
you might think but finding a suitably general format is not easy. The
specifics are
1) subsecond resolution - milliseconds or, preferably, more detailed
2) not bounded by Unix timestamp 2038 limit
3) readable in Java
4) writable portably in Perl which seems to mean that 64-bit values
are out
5) readable and writable in Python
6) storable in a free database - Postgresql/MySQL
The formats provided by the two database systems are such as 8-byte or
12-byte values which, even if I could get Perl to work with I guess it
would be messy. Keeping to 32-bit values should give me portability
and be easy enough to work with without obscuring the program logic.
Since 32 bits of microseconds is less than 50 days I have to store two
32-bit values. How to split them? The option I favour at the moment is
to split days and parts of days like this:
a) store, as a 32-bit number, days since a virtual year zero (there is
no year zero in common era time <http://en.wikipedia.org/wiki/
Common_Era>). This allows over five million years plus and minus.
Still not completely general, I know.
b) store parts of days as another 32-bit value. Its range would have
to go to 86401 seconds - the number of seconds in a leap day. This
means each ''tick'' would be around 21 microseconds. For regularity I
could make the ticks 25 microseconds so there would be 40,000 in a
second and 3,456,000,000 in a day; and, finally, the counter could
tick about 5 hours into the next day if not caught.
Any thoughts on a better way to do this? (Please reply-all. Thanks).
--
James
On Fri, 2007-06-22 at 13:33 -0700, James Harris wrote:I have a requirement to store timestamps in a database. Simple enough
you might think but finding a suitably general format is not easy. The
specifics are
1) subsecond resolution - milliseconds or, preferably, more detailed
2) not bounded by Unix timestamp 2038 limit
3) readable in Java
4) writable portably in Perl which seems to mean that 64-bit values
are out
5) readable and writable in Python
6) storable in a free database - Postgresql/MySQLPostgreSQL timestamps do not appear to be limited by Y2K38:
pgtest=create table dt(a timestamp);
CREATE TABLE
pgtest=insert into dt(a) values(''2099-01-01 01:23:45.678901'');
INSERT 0 1
pgtest=select * from dt;
a
----------------------------
2099-01-01 01:23:45.678901
(1 row)
HTH,
--
Carsten Haese
http://informixdb.sourceforge.net
James Harris wrote:a) store, as a 32-bit number, days since a virtual year zero (there is
no year zero in common era time<http://en.wikipedia.org/wiki/Common_Era>).
But according to the same article:(It [year zero] is, however, used in the astronomical system and ISO 8601.)--
Lew
On Jun 22, 10:33 am, James Harris <james.harri...@googlemail.com>
wrote:I have a requirement to store timestamps in a database. Simple enough
you might think but finding a suitably general format is not easy.
...
Any thoughts on a better way to do this? (Please reply-all. Thanks).
--
James
My rule of thumb in situations like this is "When in doubt store it as
text". The one format I am pretty sure we will still be able to deal
with in 2039.
- Roger
这篇关于便携式通用时间戳格式，不受2038限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
