[MySql]날짜, 시간 floor, ceil 처리하기

unix_timestamp, from_unixtime 을 이용하여 시간을 계산한다.

-- 5분 단위 floor
> select test_time, from_unixtime(unix_timestamp(time(test_time)) - mod(unix_timestamp(test_time), 300)) as floor_time
  from (
        select time('11:01:05') as test_time union all
        select time('11:03:55') union all
        select time('11:06:10') union all
        select time('11:09:30') union all
        select time('11:10:00') union all
        select time('11:12:59')
      ) as t;

test_time	floor_time
11:01:05	2023-02-06 11:00:00
11:03:55	2023-02-06 11:00:00
11:06:10	2023-02-06 11:05:00
11:09:30	2023-02-06 11:05:00
11:10:00	2023-02-06 11:10:00
11:12:59	2023-02-06 11:10:00



-- 1분 단위 floor
> select test_time, from_unixtime(unix_timestamp(time(test_time)) - mod(unix_timestamp(test_time), 60)) as floor_time
  from (
           select time('11:01:05') as test_time union all
           select time('11:03:55') union all
           select time('11:06:10') union all
           select time('11:09:30') union all
           select time('11:10:00') union all
           select time('11:12:59')
       ) as t;

test_time	floor_time
11:01:05	2023-02-06 11:01:00
11:03:55	2023-02-06 11:03:00
11:06:10	2023-02-06 11:06:00
11:09:30	2023-02-06 11:09:00
11:10:00	2023-02-06 11:10:00
11:12:59	2023-02-06 11:12:00



-- 5분 floor
> select test_time, from_unixtime(floor(unix_timestamp(time(test_time))/300) * 300) as floor_time
  from (
           select time('11:01:05') as test_time union all
           select time('11:03:55') union all
           select time('11:06:10') union all
           select time('11:09:30') union all
           select time('11:10:00') union all
           select time('11:12:59')
       ) as t;

test_time	floor_time
11:01:05	2023-02-06 11:00:00
11:03:55	2023-02-06 11:00:00
11:06:10	2023-02-06 11:05:00
11:09:30	2023-02-06 11:05:00
11:10:00	2023-02-06 11:10:00
11:12:59	2023-02-06 11:10:00



-- 1분 ceil
> select test_time, from_unixtime(ceil(unix_timestamp(time(test_time))/60) * 60) as floor_time
  from (
           select time('11:01:05') as test_time union all
           select time('11:03:55') union all
           select time('11:06:10') union all
           select time('11:09:30') union all
           select time('11:10:00') union all
           select time('11:12:59')
       ) as t;

test_time	floor_time
11:01:05	2023-02-06 11:02:00
11:03:55	2023-02-06 11:04:00
11:06:10	2023-02-06 11:07:00
11:09:30	2023-02-06 11:10:00
11:10:00	2023-02-06 11:10:00
11:12:59	2023-02-06 11:13:00