2

My query is the following:

SELECT count(db.user_data.page_url) AS total_urls, db.authors.author
FROM db.authors
LEFT JOIN db.user_data ON db.authors.author_id = db.user_data.id
WHERE (db.user_data.date > (now() - INTERVAL 30 MINUTE))
AND db.user_data.domain LIKE '%example.com%'
GROUP BY author;

It returns all results between now() and last 30 minutes, that's fine. But those results are based on period (interval) of 30 minutes starting from current moment (in seconds).

How to select all rows based on now() - INTERVAL 30 MINUTE ,but including all results from the last 30-th minute inclusively? Because now the interval is "moving" by current moment + 30 last minutes. So each new second I am getting different results, because of "sliding", "moving" interval?

0

2 Answers 2

4

Subtract the seconds part of NOW() from the value of NOW() and you will get the timestamp marking the beginning of the current minute. Now you can subtract your interval to get the beginning of the minute that was 30 minutes ago:

WHERE (date > (NOW() - INTERVAL SECOND(NOW()) SECOND - INTERVAL 30 MINUTE))
0
2

Another solution would be to change the date to timestamp, remove the reminder of dividing by 60 as follows:

select now();
+---------------------+
| now()               |
+---------------------+
| 2018-06-18 14:46:36 |
+---------------------+
1 row in set (0.00 sec)

select from_unixtime((unix_timestamp((now() - INTERVAL 30 MINUTE))div 60)*60) as 30_min_ago;
+---------------------+
| 30_min_ago          |
+---------------------+
| 2018-06-18 14:16:00 |
+---------------------+
1
  • Nice solution indeed, but in my situation date could not be changed. But it would work if it would be changed , i've tried in staging DB. Thanks also!
    – Sid
    Jun 18, 2018 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.