Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Rounding time to nearest hourly quarter

zebango
Member Posts: 33
I have several records like
2/19/2010 1:25:46.000000 PM
2/19/2010 1:40:46.000000 PM
2/19/2010 1:55:46.000000 PM
2/19/2010 2:10:46.000000 PM
I want to round them off to the nearest quarter like
2/19/2010 1:30:00.000000 PM
2/19/2010 1:45:00.000000 PM
2/19/2010 2:00:00.000000 PM
2/19/2010 2:15:00.000000 PM
Any ideas how I would do that? a trunc(col,'mi) only lets me round it to the minute..
Note that records are not necessarily in the 5 minute interval difference it can also be 7 minute difference or 4 minute difference from the nearest quarter...
so I can also have records like
SysUpTime 2/19/2010 10:31:00 PM
SysUpTime 2/19/2010 10:57:00 PM
SysUpTime 2/19/2010 11:31:00 PM
etc.
Edited by: zebango on Mar 9, 2010 4:16 PM
2/19/2010 1:25:46.000000 PM
2/19/2010 1:40:46.000000 PM
2/19/2010 1:55:46.000000 PM
2/19/2010 2:10:46.000000 PM
I want to round them off to the nearest quarter like
2/19/2010 1:30:00.000000 PM
2/19/2010 1:45:00.000000 PM
2/19/2010 2:00:00.000000 PM
2/19/2010 2:15:00.000000 PM
Any ideas how I would do that? a trunc(col,'mi) only lets me round it to the minute..
Note that records are not necessarily in the 5 minute interval difference it can also be 7 minute difference or 4 minute difference from the nearest quarter...
so I can also have records like
SysUpTime 2/19/2010 10:31:00 PM
SysUpTime 2/19/2010 10:57:00 PM
SysUpTime 2/19/2010 11:31:00 PM
etc.
Edited by: zebango on Mar 9, 2010 4:16 PM
Answers
-
Hi,
If you have a TIMESTAMP column t in table_x, the following will whow each one, and it's rounded-down version:SELECT t , TRUNC (t, 'HH') + ( FLOOR ( EXTRACT (MINUTE FROM (t - TRUNC (t, 'HH'))) / (60 / 4) ) / 4 ) FROM table_x;
The "magic number" 4 that appear twice in the query is the number of periods per hour.
The rounded value will be a DATE. -
alter session set nls_date_format = 'dd-MON-yy hh.mi.ss PM' / with table_x as ( select to_timestamp('2/19/2010 1:25:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') t from dual union all select to_timestamp('2/19/2010 1:40:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual union all select to_timestamp('2/19/2010 1:55:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual union all select to_timestamp('2/19/2010 2:10:46.000000 PM','mm/dd/yyyy hh:mi:ss.ff pm') from dual ) SELECT t, trunc(t,'dd') + round(to_char(t,'sssss.ff') / 900) / 96 rounded_t FROM table_x / T ROUNDED_T --------------------------------------------------------------------------- --------------------- 19-FEB-10 01.25.46.000000000 PM 19-FEB-10 01.30.00 PM 19-FEB-10 01.40.46.000000000 PM 19-FEB-10 01.45.00 PM 19-FEB-10 01.55.46.000000000 PM 19-FEB-10 02.00.00 PM 19-FEB-10 02.10.46.000000000 PM 19-FEB-10 02.15.00 PM SQL>
900 - number of seconds in 15 minutes
96 number of 15 minute intervals in a day 4 * 24
SY. -
I like case expression
with table_x as ( select timestamp '2010-02-19 01:25:46' t from dual union all select timestamp '2010-02-19 01:40:46' from dual union all select timestamp '2010-02-19 01:55:46' from dual union all select timestamp '2010-02-19 02:10:46' from dual) select t, case when mod(60*extract(minute from t)+extract(second from t),60*5) > 0 then t+NumToDsInterVal(60*5-mod(60*extract(minute from t)+extract(second from t),60*5),'SECOND') else t end as new2 from table_x; T --------------------------- NEW2 --------------------------- 10-02-19 01:25:46.000000000 10-02-19 01:30:00.000000000 10-02-19 01:40:46.000000000 10-02-19 01:45:00.000000000 10-02-19 01:55:46.000000000 10-02-19 02:00:00.000000000 10-02-19 02:10:46.000000000 10-02-19 02:15:00.000000000
-
TRUNC returns a DATE so this could be simplified even further to:
select t, trunc(t, 'hh') + floor(to_char(t, 'mi')/15)*15/1440 nearest_15_min from table_x
ETA: This rounds down to the nearest 15 minute point, which (because I clearly can't read) is not quite what the OP was after. D'oh!.
Edited by: Boneist on 11-Mar-2010 12:10 -
hi boneist
I tried your sql statementselect sysdate t, trunc(sysdate, 'hh') + floor(to_char(sysdate, 'mi')/15)*15/1440 nearest_15_min from dual
11/03/2010 11:47:12
returned
11/03/2010 11:45:00
when shouldn't it have returned with
11/03/2010 12:00:00
? -
Boneist wrote:But OP asked for "*Rounding* time to nearest hourly quarter". Same applies to Aketi's solution which, BTW, which always bumps it up to next 5 min.
TRUNC returns a DATE so this could be simplified even further to:
SY. -
DM wrote:According to OP reqs ("Rounding time to nearest hourly quarter") 11/03/2010 11:47:12 should return 11/03/2010 11:45:00. But 11/03/2010 11:52:30 should return 11/03/2010 12:00:00, while:
hi boneist
I tried your sql statementSQL> select to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss') t, 2 trunc(to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss'), 'hh') + floor(to_char(to_date('11/03/2010 11:52:30','mm/dd/yyyy hh 24:mi:ss'),'mi')/15)*15/1440 nearest_15_min 3 from dual 4 / T NEAREST_15_MIN --------------------- --------------------- 03-NOV-10 11.52.30 AM 03-NOV-10 11.45.00 AM SQL>
Now my suggestion:SQL> with table_x as ( 2 select to_date('11/03/2010 11:52:30','mm/dd/yyyy hh24:mi:ss') t from dual 3 ) 4 SELECT t, 5 trunc(t,'dd') + round(to_char(t,'sssss') / 900) / 96 rounded_t 6 FROM table_x 7 / T ROUNDED_T --------------------- --------------------- 03-NOV-10 11.52.30 AM 03-NOV-10 12.00.00 PM SQL> with table_x as ( 2 select to_date('11/03/2010 11:47:12','mm/dd/yyyy hh24:mi:ss') t from dual 3 ) 4 SELECT t, 5 trunc(t,'dd') + round(to_char(t,'sssss') / 900) / 96 rounded_t 6 FROM table_x 7 / T ROUNDED_T --------------------- --------------------- 03-NOV-10 11.47.12 AM 03-NOV-10 11.45.00 AM SQL>
SY. -
Solomon Yakobson wrote:In which case, just substitute "Round" for "Floor"!
But OP asked for "*Rounding* time to nearest hourly quarter". Same applies to Aketi's solution which, BTW, which always bumps it up to next 5 min.
Although thinking about it, you'd probably want to add seconds into the mix:with table_x as (select to_timestamp('11/03/2010 11:45:39', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 11:52:29', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 11:52:30', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 11:52:31', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 12:00:01', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 12:07:29', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 12:07:31', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 13:22:10', 'dd/mm/yyyy hh24:mi:ss') t from dual union all select to_timestamp('11/03/2010 13:22:40', 'dd/mm/yyyy hh24:mi:ss') t from dual) select t, trunc(t, 'hh') + ROUND((to_char(t, 'mi') + (to_char(t, 'ss')/60))/15)*15/1440 nearest_15_min from table_x T NEAREST_15_MIN -------------------------------- --------------------- 11/03/2010 11:45:39.000000 11/03/2010 11:45:00 11/03/2010 11:52:29.000000 11/03/2010 11:45:00 11/03/2010 11:52:30.000000 11/03/2010 12:00:00 11/03/2010 11:52:31.000000 11/03/2010 12:00:00 11/03/2010 12:00:01.000000 11/03/2010 12:00:00 11/03/2010 12:07:29.000000 11/03/2010 12:00:00 11/03/2010 12:07:31.000000 11/03/2010 12:15:00 11/03/2010 13:22:10.000000 11/03/2010 13:15:00 11/03/2010 13:22:40.000000 11/03/2010 13:30:00
-
Solomon Yakobson wrote:Ooh, seconds since midnight! {noformat}*slaps forehead*{noformat} Much better than my second attempt *{:-)
Now my suggestion: -
with table_x as (
select to_timestamp('2/19/2010 1:25:46 PM','mm/dd/yyyy hh:mi:ss pm') t from dual union all
select to_timestamp('2/19/2010 1:40:46 PM','mm/dd/yyyy hh:mi:ss pm') from dual union all
select to_timestamp('2/19/2010 1:55:46 PM','mm/dd/yyyy hh:mi:ss pm') from dual union all
select to_timestamp('2/19/2010 2:06:15 PM','mm/dd/yyyy hh:mi:ss pm') from dual
)
SELECT t,
trunc(t,'dd') + ROUND (to_char(t,'sssss') / 300) / 288 rounded_t
FROM table_x
This discussion has been closed.