TRUNC bug with droping decmil points?

[AntiEarnie]

I am stumped by this. I have been using a TRUNC statment to nip the fractions off a number and it is not behaving quite right. Some values tend to have the whole number change on instead of just droping everything right of the decmil point. For example see the query using '24-JUN-05 00:25:00' below.

[red] The query's might look a little strange, I have broken the times out of the real querys in order to demonstrate just *what* keeps happening.
[/red]
[blue]
SQL> ED
Wrote file afiedt.buf

1 select
2 (to_date('24-JUN-05 00:20:00','DD-MON-YY HH24:MI:SS')
3 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12 NO_TRUNC,
4 TRUNC((to_date('24-JUN-05 00:20:00','DD-MON-YY HH24:MI:SS')
5 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12) W_TRUNC
6* FROM DUAL
SQL> /

NO_TRUNC W_TRUNC
--------- ---------
-1436 -1436

SQL> ED
Wrote file afiedt.buf

1 select
2 (to_date('24-JUN-05 00:24:00','DD-MON-YY HH24:MI:SS')
3 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12 NO_TRUNC,
4 TRUNC((to_date('24-JUN-05 00:24:00','DD-MON-YY HH24:MI:SS')
5 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12) W_TRUNC
6* FROM DUAL
SQL> /

NO_TRUNC W_TRUNC
--------- ---------
-1435.2 -1435

SQL> ED
Wrote file afiedt.buf

1 select
2 (to_date('24-JUN-05 00:25:00','DD-MON-YY HH24:MI:SS')
3 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12 NO_TRUNC,
4 TRUNC((to_date('24-JUN-05 00:25:00','DD-MON-YY HH24:MI:SS')
5 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12) W_TRUNC
6* FROM DUAL
SQL> /

NO_TRUNC W_TRUNC
--------- ---------
-1435 -1434

SQL> ED
Wrote file afiedt.buf

1 select
2 (to_date('24-JUN-05 00:26:00','DD-MON-YY HH24:MI:SS')
3 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12 NO_TRUNC,
4 TRUNC((to_date('24-JUN-05 00:26:00','DD-MON-YY HH24:MI:SS')
5 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12) W_TRUNC
6* FROM DUAL
SQL> /

NO_TRUNC W_TRUNC
--------- ---------
-1434.8 -1434

SQL> ED
Wrote file afiedt.buf

1 select
2 (to_date('24-JUN-05 00:30:00','DD-MON-YY HH24:MI:SS')
3 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12 NO_TRUNC,
4 TRUNC((to_date('24-JUN-05 00:30:00','DD-MON-YY HH24:MI:SS')
5 - TO_DATE('29-JUN-05 00:00:00', 'DD-MON-YY HH24:MI:SS'))*24*12) W_TRUNC
6* FROM DUAL
SQL> /

NO_TRUNC W_TRUNC
--------- ---------
-1434 -1434
[/blue]


Can anyone give me an idea of how to get around this? Or another way to just drop the fraction off the number?

 

[AntiEarnie]

As a side note the example sql where TRUNC seems to be glitching "works" on our 8i and 9i instance.

 

[SantaMufasa]

First of all, Anti, right now you are asking for the difference between a couple of dozen minutes past midnight on 24-JUN and midnight on 29-JUN in units of 12 minutes, which is a tad imperceptible to me. (Out of curiosity, why are you interested in 12-minute units?)

Now to the crux of the matter: The raw math says that the initial calculation will be just a few minutes under 5 full days. Then you ask to translate the answers from days to hours ("...* 24"), then from hours to 12-minute increments ("...*12"). Depending upon which number of minutes you start with, the non-truncated solution hovers around -1435 12-minute increments (+/- some very small decimals). Oracle displays the non-truncated result, rounded to its internal significant digits behind the decimal. If the result is smaller than Oracle's internal digits of significance, then you end up with -1435 instead of -1434.0000000000000000027. But if you TRUNC(-1434.0000000000000000027), your result is -1434 instead of -1435.

So, before I can directly answer your question:

Can anyone give me an idea of how to get around this? Or another way to just drop the fraction off the number?

...I need to know what level of accuracy your business need demands, or just what you are looking for, then I can propose a solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

BTW, Anti, I should clarify that is a purposely programmed behaviour, not a "bug" or a "glitch".

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[AntiEarnie]

SantaMufasa, sorry didn't want to confuse the issue. The origional statment is used in a "GROUP BY TRUNC((TIMESTAMP-ROUND(SYSDATE))*24*SomeNumber". The statment origionally is placed in GROUP BY when we query against large data tables in order to summarize data (i.e. 5min, 15 min, 1hr instead of 3sec source data). I broke out what the table had for timestamp and sysdate in order to troubleshoot and reliable reproduce what I was seeing.

Previously my source data table was in incriments of 3 seconds. In that case having one point shift to a different incriment was not very noticable, even in grouped one minute incriments. However, I was trying to use this GROUP BY statment with a table containing data in incriments of 1 minute. As a result the occastional shift of a point to the wrong grouped incriment is MUCH more noticable since there are fewer source points per group incriment.

Since I have probably confused the issue I will drop in a working example of the test query. I have shifted the math from the GROUP BY up to the select in order to see how it would be grouped.

This would be using the TRUNC((TIMESTAMP-ROUND(SYSDATE))*24*12 in order to make 5 minute min/max/average data. PLAIN is value before the trunc.

[blue]
TIMESTAMP PLAIN TRUNCED
6/24/2005 0:01 -1439.8 -1439
6/24/2005 0:02 -1439.6 -1439
6/24/2005 0:03 -1439.4 -1439
6/24/2005 0:04 -1439.2 -1439
6/24/2005 0:05 -1439 -1439
6/24/2005 0:06 -1438.8 -1438
6/24/2005 0:07 -1438.6 -1438
6/24/2005 0:08 -1438.4 -1438
6/24/2005 0:09 -1438.2 -1438
6/24/2005 0:10 -1438 -1437
6/24/2005 0:11 -1437.8 -1437
6/24/2005 0:12 -1437.6 -1437
6/24/2005 0:13 -1437.4 -1437
6/24/2005 0:14 -1437.2 -1437
6/24/2005 0:15 -1437 -1437
[/blue]

If I had left the GROUP BY in place the above statment should have been grouped down into 3 rows with 5 entries per row. With the TRUNC changing what appears to be left of the decmil we get 3 rows with 5/4/6 entries per row.

I am not really sure I understand what you were talking about on the significant digits. If the number was that small would it would still be rounded up instead of to the nearest digit?

 

[SantaMufasa]

Anti, now that I more fully understand your need, for the "equal" treatment you want, try ceil(...) instead of trunc(...). Please re-run your query with that change (and begin at :00 instead of :01). Then we can evaluate if that improves your results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[AntiEarnie]

SantaMufasa - No luck with the ceil function either. Just for giggles I tried using floor and could see something similar.
[blue]
TIMESTAMP PLAIN TRUNCED CEILED FLOORED
6/24/2005 0:00 -1440 -1440 -1440 -1440
6/24/2005 0:01 -1439.8 -1439 -1439 -1440
6/24/2005 0:02 -1439.6 -1439 -1439 -1440
6/24/2005 0:03 -1439.4 -1439 -1439 -1440
6/24/2005 0:04 -1439.2 -1439 -1439 -1440
6/24/2005 0:05 -1439 -1439 -1439 -1440
6/24/2005 0:06 -1438.8 -1438 -1438 -1439
6/24/2005 0:07 -1438.6 -1438 -1438 -1439
6/24/2005 0:08 -1438.4 -1438 -1438 -1439
6/24/2005 0:09 -1438.2 -1438 -1438 -1439
6/24/2005 0:10 -1438 -1437 -1437 -1438
6/24/2005 0:11 -1437.8 -1437 -1437 -1438
6/24/2005 0:12 -1437.6 -1437 -1437 -1438
6/24/2005 0:13 -1437.4 -1437 -1437 -1438
6/24/2005 0:14 -1437.2 -1437 -1437 -1438
6/24/2005 0:15 -1437 -1437 -1437 -1437
[/blue]

I am still trying to work out the internal rounding you mentioned above. I get the internal Oracle value is a bit different then what I see. After all, if I just trunc -1438 I am not going to get -1437. It is just confusing when I get a -1438 from both sql+ and my odbc connection. Also, I can see what you are refering to if Oracle always rounds up ininternaly. It just seems odd that when I query the DB it rounds to nearest, not rounding up.

 

[AntiEarnie]

Ah, ok. Someone just pointed out to me that if it is a repeating .9 or at least a .9 out beyond what sql+ can display then what I would see would be just a whole number after the internal rounding. By wrapping the plain value in a round, then doing a trunc it cleaned up.

TIMESTAMP PLAIN TRUNCED CEILED FLOORED TRUNC_ROUND
6/24/2005 0:00 -1440 -1440 -1440 -1440 -1440
6/24/2005 0:01 -1439.8 -1439 -1439 -1440 -1439
6/24/2005 0:02 -1439.6 -1439 -1439 -1440 -1439
6/24/2005 0:03 -1439.4 -1439 -1439 -1440 -1439
6/24/2005 0:04 -1439.2 -1439 -1439 -1440 -1439
6/24/2005 0:05 -1439 -1439 -1439 -1440 -1439
6/24/2005 0:06 -1438.8 -1438 -1438 -1439 -1438
6/24/2005 0:07 -1438.6 -1438 -1438 -1439 -1438
6/24/2005 0:08 -1438.4 -1438 -1438 -1439 -1438
6/24/2005 0:09 -1438.2 -1438 -1438 -1439 -1438
6/24/2005 0:10 -1438 -1437 -1437 -1438 -1438
6/24/2005 0:11 -1437.8 -1437 -1437 -1438 -1437
6/24/2005 0:12 -1437.6 -1437 -1437 -1438 -1437
6/24/2005 0:13 -1437.4 -1437 -1437 -1438 -1437
6/24/2005 0:14 -1437.2 -1437 -1437 -1438 -1437
6/24/2005 0:15 -1437 -1437 -1437 -1437 -1437

It bothers me a great deal that I forgot to take into account something this obvious. Espically after you mentioned it SantaMufasa.

Be nice if I can work out how to get this to group from 0-4 instead of 1-5 min blocks. At least the blocks have an even number of records in them now though.