'Rounding of timestamp to nearest second
I've got a table containing timestamp values, and I want to round each of these values to the nearest second, but I can't get it work properly.
My test data and approaches so far:
with v_data as
(select to_timestamp('2012-12-10 10:49:30.00000000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:30',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.46300000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:30',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.50000000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.56300000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.99999999',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual)
select v1.base_val,
v1.expected,
v1.base_val + (0.5 / 86400) solution_round,
cast(v1.base_val as date) as solution_cast,
extract(second from v1.base_val) - trunc(extract(second from v1.base_val)) fractional_seconds,
v1.base_val -
(extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add
from v_data v1
All my solutions have a flaw:
- solution_round always rounds up
- solution_cast works up to 11gR1, but in 11gR2, it always rounds down (cause: Oracle changed the behaviour - it now truncates instead of rounding, see https://forums.oracle.com/forums/thread.jspa?threadID=2242066 )
- solution_add returns 10:49:29 instead of 10:49:31 for the last three rows
I guess solution_add should work, and I just made some stupid mistake :-)
EDIT:
Ben's solution (see below) works for me, but relying on to_char(timestamp, 'FF') seems to be dangerous - the number of digits returned depends on the definition of the timestamp.
I'm using to_char(timestamp, 'FF3') instead, which seems to return milliseconds reliably.
Solution 1:[1]
My preferred method would be to use a CASE statement and the fact that you can convert the fractional seconds to a number, i.e.:
select base_val, expected
, to_timestamp(to_char(base_val,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD HH24:mi:ss')
+ case when to_number(to_char(base_val, 'FF8')) >= 50000000
then interval '1' second
else interval '0' second
end as solution_add
from v_data
This removes the fractional seconds. Then works out whether the fractional seconds portion of your TIMESTAMP is 0.5 seconds, or more. If so then add a second, otherwise don't.
I find it a lot clearer and easier to understand what's going on. It returns the following:
with v_data as
(select to_timestamp('2012-12-10 10:49:30.00000000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:30',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.46300000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:30',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.50000000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.56300000',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
union all
select to_timestamp('2012-12-10 10:49:30.99999999',
'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
to_timestamp('2012-12-10 10:49:31',
'YYYY-MM-DD HH24:mi:ss') expected
from dual
)
select base_val, expected
, to_timestamp(to_char(base_val, 'YYYY-MM-DD HH24:mi:ss'), 'YYYY-MM-DD HH24:mi:ss')
+ case when to_number(to_char(base_val, 'FF8')) >= 50000000
then interval '1' second
else interval '0' second
end as solution_add
from v_data;
BASE_VAL EXPECTED SOLUTION_ADD
---------------------------- ---------------------------- ----------------------------
10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000
10-DEC-12 10.49.30.463000000 10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000
10-DEC-12 10.49.30.500000000 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000
10-DEC-12 10.49.30.563000000 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000
10-DEC-12 10.49.30.999999990 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000
Solution 2:[2]
your final approach works (solution_add) its just that you used - instead of +. the 11g behavious is due to a bug fix (in 10g and below, plsql used to behave as "trunc" when cast was used, whereas SQL behaved as round. Oracle decided PLSQL was right, and changed 11g accordingly.
i.e. use:
v1.base_val +
(extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add
though i'd probably specify it explicitly to take away the implicit conversion from timestamp to date (avoiding the dodgy cast())
to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
(( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400)
eg:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> with v_data as
2 (select to_timestamp('2012-12-10 10:49:30.00000000',
3 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
4 to_timestamp('2012-12-10 10:49:30',
5 'YYYY-MM-DD HH24:mi:ss') expected
6 from dual
7 union all
8 select to_timestamp('2012-12-10 10:49:30.46300000',
9 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
10 to_timestamp('2012-12-10 10:49:30',
11 'YYYY-MM-DD HH24:mi:ss') expected
12 from dual
13 union all
14 select to_timestamp('2012-12-10 10:49:30.50000000',
15 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
16 to_timestamp('2012-12-10 10:49:31',
17 'YYYY-MM-DD HH24:mi:ss') expected
18 from dual
19 union all
20 select to_timestamp('2012-12-10 10:49:30.56300000',
21 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
22 to_timestamp('2012-12-10 10:49:31',
23 'YYYY-MM-DD HH24:mi:ss') expected
24 from dual
25 union all
26 select to_timestamp('2012-12-10 10:49:30.99999999',
27 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
28 to_timestamp('2012-12-10 10:49:31',
29 'YYYY-MM-DD HH24:mi:ss') expected
30 from dual)
31 select v1.base_val,
32 v1.expected,
33 v1.base_val +
34 (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add,
35 to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
36 (( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400) solution_add2
37 from v_data v1;
BASE_VAL EXPECTED SOLUTION_ADD SOLUTION_ADD2
------------------------- ------------------------- ------------------------- -------------------------
10-dec-12 10:49:30000 10-dec-12 10:49:30000 10-dec-2012 10:49:30 10-dec-2012 10:49:30
10-dec-12 10:49:30463 10-dec-12 10:49:30000 10-dec-2012 10:49:30 10-dec-2012 10:49:30
10-dec-12 10:49:30500 10-dec-12 10:49:31000 10-dec-2012 10:49:31 10-dec-2012 10:49:31
10-dec-12 10:49:30563 10-dec-12 10:49:31000 10-dec-2012 10:49:31 10-dec-2012 10:49:31
10-dec-12 10:49:30999 10-dec-12 10:49:31000 10-dec-2012 10:49:31 10-dec-2012 10:49:31
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> with v_data as
2 (select to_timestamp('2012-12-10 10:49:30.00000000',
3 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
4 to_timestamp('2012-12-10 10:49:30',
5 'YYYY-MM-DD HH24:mi:ss') expected
6 from dual
7 union all
8 select to_timestamp('2012-12-10 10:49:30.46300000',
9 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
10 to_timestamp('2012-12-10 10:49:30',
11 'YYYY-MM-DD HH24:mi:ss') expected
12 from dual
13 union all
14 select to_timestamp('2012-12-10 10:49:30.50000000',
15 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
16 to_timestamp('2012-12-10 10:49:31',
17 'YYYY-MM-DD HH24:mi:ss') expected
18 from dual
19 union all
20 select to_timestamp('2012-12-10 10:49:30.56300000',
21 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
22 to_timestamp('2012-12-10 10:49:31',
23 'YYYY-MM-DD HH24:mi:ss') expected
24 from dual
25 union all
26 select to_timestamp('2012-12-10 10:49:30.99999999',
27 'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
28 to_timestamp('2012-12-10 10:49:31',
29 'YYYY-MM-DD HH24:mi:ss') expected
30 from dual)
31 select v1.base_val,
32 v1.expected,
33 v1.base_val +
34 (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add,
35 to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
36 (( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400) solution_add2
37 from v_data v1;
BASE_VAL EXPECTED SOLUTION_ADD SOLUTION_ADD2
------------------------- ------------------------- ------------------------- -------------------------
10-dec-12 10:49:30000 10-dec-12 10:49:30000 10-dec-12 10:49:30 10-dec-12 10:49:30
10-dec-12 10:49:30463 10-dec-12 10:49:30000 10-dec-12 10:49:30 10-dec-12 10:49:30
10-dec-12 10:49:30500 10-dec-12 10:49:31000 10-dec-12 10:49:31 10-dec-12 10:49:31
10-dec-12 10:49:30563 10-dec-12 10:49:31000 10-dec-12 10:49:31 10-dec-12 10:49:31
10-dec-12 10:49:30999 10-dec-12 10:49:31000 10-dec-12 10:49:31 10-dec-12 10:49:31
Solution 3:[3]
I am very, very late to this discussion but just in case someone searches, there is a one-line answer to this question:
First CAST to a TIMESTAMP(0) - it has no fractional seconds so they will be rounded. Then CAST the result to a DATE
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
with data as (select sys_extract_utc(localtimestamp) ts from dual)
select ts, cast(cast(ts as timestamp(0)) as date) dte
from data;
Solution 4:[4]
Have you tried converting to_date and doing the rounding at that point?
http://www.java2s.com/Tutorial/Oracle/0260__Date-Timestamp-Functions/RoundingtotheNearestMinute.htm
You should then be able to convert to timestamp.
Obviously change the 'MI' on line 3 to 'SS'
---UPDATE---
Casting to a date will round to the nearest second as the DATE datatype has a precision of seconds, so cast to a date and then cast to a timestamp:
to_timestamp(CAST( '2012-12-10 10:49:30.99999999' AS DATE))
Solution 5:[5]
This is T-SQL (not sure if it works on Oracle)
DECLARE @tblTime TABLE (ID INT, myTime DATETIME2);
INSERT INTO @tblTime SELECT 1, GETDATE()
SELECT myTime
, CONVERT( DATETIME2, CONVERT( VARCHAR, DATEADD( SECOND, CASE WHEN DATEPART( MS, myTime) >= 500 THEN 1 ELSE -1 END, myTime ), 20), 20 ) AS timeRounded
FROM @tblTime
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Jon Heller |
| Solution 2 | |
| Solution 3 | Stew Ashton |
| Solution 4 | |
| Solution 5 | Joseph Lee |