Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as expected. I want to know if there is any better way...

mysql> select now() ;
+---------------------+
| now()               |
+---------------------+
| 2012-03-11 08:43:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT date_sub(from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300), interval 5 minute) as ten_minute_interval,  from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300) as five_minute_interval;
+---------------------+----------------------+
| ten_minute_interval | five_minute_interval |
+---------------------+----------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00  |
+---------------------+----------------------+
1 row in set (0.00 sec)


mysql> SELECT some better query...
share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

Here is the query that will give you the most recent 5 minute interval

select
    fm_ending - interval 5 minute fm_beg,fm_ending
from 
    (
        select gt
            - interval mod(minute(gt),5) minute
            - interval second(gt) second fm_ending
        from
            (select now() gt) giventime
    ) A
;

To test it, insert anything you want for gt

Example : You mentioned '2012-03-11 08:43:06'

mysql> select
    ->     fm_ending - interval 5 minute fm_beg,fm_ending
    -> from
    ->     (
    ->         select gt
    ->             - interval mod(minute(gt),5) minute
    ->             - interval second(gt) second fm_ending
    ->         from
    ->             (select '2012-03-11 08:43:06' gt) giventime
    ->     ) A
    -> ;
+---------------------+---------------------+
| fm_beg              | fm_ending           |
+---------------------+---------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00 |
+---------------------+---------------------+
1 row in set (0.02 sec)

mysql>

Give it a Try !!!

share|improve this answer
add comment

Try this query

    select date_add(now(),interval -10 minute) as ten_minute_interval,
    date_add(now(), interval -5 minute) as five_minute_interval,
    now() as Current_time
share|improve this answer
add comment

select now() as 'Current Time', timestampadd(minute,-5,now()) as 'Five_minute_interval', timestampadd(minute,-10,now()) as 'Ten_minute_interval'

You can try this

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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