Mysql – Finding five-minute interval prior to current time

functionsMySQLtimestamp

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...

Best Answer

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 !!!

Related Question