主页 / dba / 问题 / 14762
Accepted
shantanuo
Asked: 2012-03-11 19:16:16 +0800 CST

查找当前时间之前的五分钟间隔

  • 772

我想找到当前时间之前的五分钟间隔。因此，如果当前时间是 8:43，我想返回 8:35 和 8:40。这是我写的查询，它按预期工作。我想知道有没有更好的方法...

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

3 个回答

  1. 试试这个查询

        select date_add(now(), interval -5 minute) five_mins_interval,
    date_add(now(), interval -10 minute) ten_mins_interval, now() 
    currentTime
    • 3
  2. Best Answer

    这是将为您提供最近 5 分钟间隔的查询

    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
;

    要对其进行测试，请为 gt 插入您想要的任何内容

    示例：您提到“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>

    试试看 ！！！

    • 1

  3. 选择 now() 作为“当前时间”，timestampadd(minute,-5,now()) 作为“Five_minute_interval”，timestampadd(minute,-10,now()) 作为“Ten_minute_interval”

    你可以试试这个

    • 0

相关问题