dpf5207
2013-08-02 09:24
浏览 239
已采纳

将unix时间戳存储为无符号整数会有什么好处吗?

I don't want to start another discussion of pro or contra between using DATETIME vs TIMESTAMP vs INT. (I've already read articles like Using MySQL's TIMESTAMP vs storing timestamps directly.)

I sometimes use INT data type to store unix timestamps in database. That's because date and time calculations in my applications where done with unix timestamps often (e.g. for session timeouts and token expiration). Furthermore data selection in database is faster than using DATETIME when I can compare integer values in WHERE clause simply. There are few tables with 10+ million rows (up to 100 million) where this 4 bytes really save storage (on disk and in memory because of smaller indexes) also.

Regarding Y2K38 problem, I assume (and hope so), that UNIX_TIMESTAMP in MySQL an time() in PHP will return 64bit values in future, so there will be no need to change something in the applications itself. The point is, I've stored all these pseudo integer timestamp in MySQL as unsigned integers (I mean INT not BIGINT). Sure, unsigned integer timestamps will overflow in year 2106, but that's a bit more time than 2038.

My question is now: assuming that UNIX_TIMESTAMP itself will work after 2038, could there be any problems in MySQL and/or PHP until 2106 when these timestamps are stored as unsigned integer in MySQL? (Please don't argue with: there will be a lot time to fix that until 2038, I want to clarify this from the point of the application are not touched any longer)

EDIT: Because the question came up: I store only current timestamps in these columns, no birthdates, no future dates. Only current timestamps, so I want to clarify if this would work after 2038.

图片转代码服务由CSDN问答提供 功能建议

我不想在使用 DATETIME vs开始另一个关于pro或contra的讨论 TIMESTAMP vs INT 。 (我已经阅读了使用MySQL的TIMESTAMP直接存储时间戳。)

我有时使用 INT 数据类型在数据库中存储unix时间戳。 这是因为我的应用程序中的日期和时间计算经常使用unix时间戳(例如,对于会话超时和令牌到期)。 此外,当我可以简单地比较 WHERE 子句中的整数值时,数据库中的数据选择比使用 DATETIME 更快。 很少有表有超过100万行(高达1亿),这4个字节确实可以节省存储空间(因为索引较小,在磁盘和内存中)。

关于Y2K38问题, 我假设(并希望如此),MySQL中的 UNIX_TIMESTAMP 和PHP中的 time()将来会返回64位值,因此不需要更改 应用本身。 关键是,我将所有这些伪整数时间戳存储在MySQL中作为无符号整数(我的意思是 INT 而不是 BIGINT )。 当然,无符号整数时间戳将在2106年溢出,但这比2038时更多。

现在我的问题是:假设 UNIX_TIMESTAMP 本身将在以后工作 2038,这些时间戳在MySQL中存储为无符号整数,直到2106,MySQL和/或PHP会出现任何问题吗? (请不要争论:将有很多时间来解决这个问题,直到2038年,我想从应用程序的角度来澄清这一点,不再触及)

编辑:因为问题出现了:我只在这些列中存储当前时间戳,没有生日,没有未来日期。 只有当前的时间戳,所以我想澄清这是否会在2038年之后起作用。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • drnzpd579935 2013-08-28 12:15
    已采纳

    Your assumption surrounding UNIX_TIMESTAMP() is a big one.

    Currently, UNIX_TIMESTAMP returns 0 if you try

    mysql> select unix_timestamp("2038-01-19" );
    +-------------------------------+
    | unix_timestamp("2038-01-19" ) |
    +-------------------------------+
    |                    2147468400 |
    +-------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select unix_timestamp("2038-01-20");
    +------------------------------+
    | unix_timestamp("2038-01-20") |
    +------------------------------+
    |                            0 |
    +------------------------------+
    1 row in set (0.00 sec)
    

    While the storage of INTs longer than 32-bits will work, unless you know something about how the implementation of unix_timestamp(int64) will work, then the question is really more guesswork than facts.

    This implies that any integer arithmetic you do will still be valid with 64-bit ints, so for finding expired sessions (timestamp + timeout < (seconds since 1970 in 64-bits)) will still work. Whether or not you can rely on from_unixtime() and unix_timestamp()-functions depend whether the solution is just to up the ante to 64-bits or if the whole world in the next 20-odd years decide to set a new epoch.

    Nobody knows for sure.

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题