MySQL timestamp 30 day validity from moment of creation
MySQL timestamp 30 day validity from moment of creation
What I am trying to do here is adding an 30 DAYS to the timestamp when the session key was created.
30 DAYS
timestamp
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).
timestamp
UTC_TIMESTAMP
I can ADD another condition AND (timestamp + INTERVAL 30 DAY) >= UTC_TIMESTAMP(), but I would have to calculate the interval again.
AND (timestamp + INTERVAL 30 DAY) >= UTC_TIMESTAMP()
Is there a more efficient way to have this done?
3 Answers
3
Try this instead:
`timestamp` >= DATE_SUB(NOW(), INTERVAL 30 DAY)
UNIX_TIMESTAMP
UTC_TIMESTAMP
Can you please run this query and report back the results? It will be helpful in debugging this issue: SELECT UNIX_TIMESTAMP(), MIN(
timestamp) as min_time, MAX(timestamp) as max_time from sessions– Tomer Shay
Sep 11 '18 at 19:35
timestamp
timestamp
sessions
min and max time are the same at
2018-09-08 18:33:36.000000. Thats cause I've only got the one session I test with.– Borsn
Sep 11 '18 at 19:39
2018-09-08 18:33:36.000000
Edited my answer, try the new condition please.
– Tomer Shay
Sep 11 '18 at 19:43
Works. Why would I use
DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY) over (UTC_TIMESTAMP() - INTERVAL 40 DAY) are there any advantages?– Borsn
Sep 11 '18 at 20:04
DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)
(UTC_TIMESTAMP() - INTERVAL 40 DAY)
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()
(timestamp + INTERVAL 30 DAY) <= UTC_TIMESTAMP()
is usually better handled as: timestamp <= (UTC_TIMESTAMP() - INTERVAL 30 DAY)
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.
timestamp
timestamp
Summary: When possible, apply calculations to the sides of conditions/comparisons that are not dependent on the row data.
You can look up about sargeable query.
– Juan Carlos Oropeza
Sep 11 '18 at 19:28
I'v implemented this
timestamp <= (UTC_TIMESTAMP() - INTERVAL 40 DAY). And I don't get any results. Went as far back as 500 days.– Borsn
Sep 11 '18 at 19:30
timestamp <= (UTC_TIMESTAMP() - INTERVAL 40 DAY)
Your using the wrong comparation. should be
timestamp >= think about it. Today is september 10, substract 30 days. if timestamp is >= august 10 the session is valid– Juan Carlos Oropeza
Sep 11 '18 at 19:32
timestamp >=
@Borsn from the data you've mentioned, that sounds like the correct result. You have no data older than a few days, looking further back won't find more; as Juan said, check that your condition is appropriate; the one in your comment is looking for "data older than 40 days"
– Uueerdo
Sep 11 '18 at 19:33
@JuanCarlosOropeza Now that works great. I was going off the suggestion in the answer.
– Borsn
Sep 11 '18 at 19:37
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;
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
My time is kept as datetime(6). I've tried with
UNIX_TIMESTAMPandUTC_TIMESTAMPgetting 0 rows.– Borsn
Sep 11 '18 at 19:33