如何將小時資料四舍五入到季度？
Eg.
| Hour (HH24:MI:SS) | Expected result | Query result |
|--|--|--|
| 10:04:00| 10:00:00|10:00:00|
| 10:12:00| 10:00:00|10:15:00|
| 10:14:59| 10:00:00|10:15:00|
| 10:15:00| 10:15:00|10:15:00|
| 10:15:02| 10:15:00|10:15:00|
| 10:16:00| 10:15:00|10:15:00|
我寫了這樣的東西，但它只四舍五入到最近的四分之一：/
with dates as (
select to_date('01/01/2022 10:04:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:12:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:14:59', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:02', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:16:00', 'mm/dd/yyyy hh:mi:ss') d from dual
)
select d
,TRUNC (d) (ROUND ((d - TRUNC (d)) * 96) / 96)
from dates;
uj5u.com熱心網友回復：
您可以使用MODulo 函式查找要減去的分鐘數和秒數：
with dates as (
select to_date('01/01/2022 10:04:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:12:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:14:59', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:02', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:16:00', 'mm/dd/yyyy hh:mi:ss') d from dual
)
select d,
d - MOD((d - TRUNC(d))*24*60, 15)/24/60 AS rounded
from dates;
哪個輸出：
D 圓潤的 2022-01-01 10:04:00 2022-01-01 10:00:00 2022-01-01 10:12:00 2022-01-01 10:00:00 2022-01-01 10:14:59 2022-01-01 10:00:00 2022-01-01 10:15:00 2022-01-01 10:15:00 2022-01-01 10:15:02 2022-01-01 10:15:00 2022-01-01 10:16:00 2022-01-01 10:15:00
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/425311.html