strart time과 endtime이 있습니다.
예를 들면
start_time : 2023-03-15 09:20:00
end_time : 2023-03-15 13:05:00
이라고 한다면 해당 범위의 시간대를 가지고 start_time과 end_time의 레코드를 생성하고 싶습니다
출력결과를 다음과 같이 하고 싶습니다.
start_time | end_time |
2023-03-15 09:20:00 | 2023-03-15 09:59:59 |
2023-03-15 10:00:00 | 2023-03-15 10:59:59 |
2023-03-15 11:00:00 | 2023-03-15 11:59:59 |
2023-03-15 12:00:00 | 2023-03-15 12:59:59 |
2023-03-15 13:00:00 | 2023-03-15 13:05:00 |
그럼 의견 부탁드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | WITH date_range AS ( SELECT TIMESTAMP '2023-03-15 09:20:00' AS start_time, TIMESTAMP '2023-03-15 13:05:00' AS end_time ), hour_range AS ( SELECT date_trunc( 'hour' , start_time) + (n - 1) * INTERVAL '1 hour' AS hour_start, date_trunc( 'hour' , start_time) + n * INTERVAL '1 hour' - INTERVAL '1 second' AS hour_end FROM date_range, generate_series(1, CEIL(EXTRACT(epoch FROM (end_time - start_time))/3600):: integer + 1) AS n ) SELECT CASE WHEN start_time > hour_start THEN start_time ELSE hour_start END AS start_time, CASE WHEN end_time < hour_end THEN end_time ELSE hour_end END AS end_time FROM date_range, hour_range WHERE hour_start < end_time ORDER BY start_time; |