dongyiba8082
2018-09-11 18:56
浏览 363
已采纳

MySQL时间戳从创建时起30天有效期

What I am trying to do here is adding an 30 DAYS to the timestamp when the session key was created.

SELECT * FROM `sessions` 
WHERE (`timestamp` + INTERVAL 30 DAY) <= UTC_TIMESTAMP()

And then checking it to the current time.


But this doesn't work because the session was created just a few days ago. So the timestamp is way larger than UTC_TIMESTAMP (the current time).

I can ADD another condition AND (timestamp + INTERVAL 30 DAY) >= UTC_TIMESTAMP(), but I would have to calculate the interval again.


Is there a more efficient way to have this done?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • drg17404 2018-09-11 19:30
    已采纳

    Try this instead:

    `timestamp` >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    
    已采纳该答案
    打赏 评论
  • doucitan2544 2018-09-11 19:25

    I'm not quite sure on the logic you are looking for, but as far as "more efficient way":

    your original condition of: (timestamp + INTERVAL 30 DAY) <= UTC_TIMESTAMP()

    is usually better handled as: timestamp <= (UTC_TIMESTAMP() - INTERVAL 30 DAY)

    In your original, MySQL will apply the addition of the interval to every row's timestamp value (and end up ignoring any indexes if you have them); in the rewrite I provided, the interval should be subtracted only once from the result of UTC_TIMESTAMP(), and taking advantage of indexed on timestamp is still an option.

    Summary: When possible, apply calculations to the sides of conditions/comparisons that are not dependent on the row data.

    打赏 评论
  • dongshanni1611 2018-09-11 19:39

    If a session is valid 30 days from it's creation and you only want to select valid sessions, you need to check, that the creation is in the past but not more than 30 days past now.

    SELECT *
           FROM seeions
           WHERE timestamp < utc_timestamp()
                 AND timestamp >= utc_timestamp() - INTERVAL 30 DAY;
    
    打赏 评论

相关推荐 更多相似问题