How does Toad format interval datatypes?

Toad for Oracle - Beta
1

Howdy,

In the Editor in .41, I ran this guy:

SELECT NUMTODSINTERVAL(996300,‘SECOND’) FROM DUAL;

…which produces a grid with the value of: “+11 12:45:00.000000”

If I choose "Execute via SQLPlus", I get “+000000011 12:45:00.000000000”, and I confirmed that a separate SQLPlus window using the same Oracle Home as Toad generated that same output.

I couldn’t find any Toad options to format this, so how does Toad do it?

TIA!
Rich

2

Our Oracle connectivity layer provides the format and as far as I can tell, it’s not configurable.

3

Sounds good. I have intervals in a script that I was trying to format, which surprisingly isn’t straightforward, so I was hoping that Toad’s magic could help decipher it. I ended up wrapping the interval in a funless REGEXP_SUBSTR call, as TO_CHAR offers exactly zero options in formatting them.

Thanks for checking for me, John!

Rich

4

it’s a pain there is no NLS_INTERVAL_FORMAT. It would be worth rising an ER by Oracle :slight_smile:

You could also try some like hack with date or use extract

   CREATE TABLE t
AS
SELECT NUMTODSINTERVAL (-996300.000001, 'SECOND') i FROM DUAL
UNION ALL
SELECT NUMTODSINTERVAL (-996.000001, 'SECOND') i FROM DUAL
UNION ALL
SELECT NUMTODSINTERVAL (996.000001, 'SECOND') i FROM DUAL
UNION ALL
SELECT NUMTODSINTERVAL (996300.000001, 'SECOND') i FROM DUAL;
SELECT i,
CASE
WHEN i <= INTERVAL '-1' DAY
THEN
TO_CHAR (TIMESTAMP '1999-12-31 00:00:00.000000000' - i,
'-FMDDDFM HH24:MI:SSxFF')
WHEN i < INTERVAL '0' DAY
THEN
TO_CHAR (TIMESTAMP '1999-12-31 00:00:00.000000000' - i,
'-"0" HH24:MI:SSxFF')
WHEN i <= INTERVAL '1' DAY
THEN
TO_CHAR (TIMESTAMP '1999-12-31 00:00:00.000000000' + i,
'+"0" HH24:MI:SSxFF')
ELSE
TO_CHAR (TIMESTAMP '1999-12-31 00:00:00.000000000' + i,
'+FMDDDFM HH24:MI:SSxFF')
END
tochar,
CASE WHEN i < INTERVAL '0' SECOND THEN '-' ELSE '+' END
|| TO_CHAR (ABS (EXTRACT (DAY FROM i)), 'FM990')
|| ' '
|| TO_CHAR (ABS (EXTRACT (HOUR FROM i)), 'FM00')
|| ':'
|| TO_CHAR (ABS (EXTRACT (MINUTE FROM i)), 'FM00')
|| ':'
|| TO_CHAR (ABS (EXTRACT (SECOND FROM i)), 'FM00D99999999999')
EXTRACT
FROM t;
I TOCHAR EXTRACT ------------------------------ ------------------------------ ------------------------------
-11 12:45:00.000001 -11 12:45:00.000001000 -11 12:45:00.000001 -00 00:16:36.000001 -0 00:16:36.000001000 -0 00:16:36.000001 +00 00:16:36.000001 +0 00:16:36.000001000 +0 00:16:36.000001 +11 12:45:00.000001 +11 12:45:00.000001000 +11 12:45:00.000001
5

Hey Laurent!

This script of mine was written in the late 90s to show, in part, session inactivity in Oracle7, so it had the inelegant solution of:

SELECT
TO_CHAR(TRUNC(last_call_et/86400),‘FM00’) || ‘::’ ||
TO_CHAR(TRUNC(MOD(last_call_et,86400)/3600),‘FM00’) || ‘:’ ||
TO_CHAR(TRUNC(MOD(MOD(last_call_et,86400),3600)/60),‘FM00’) || ‘:’ ||
TO_CHAR(MOD(MOD(MOD(last_call_et,86400),3600),60),‘FM00’) “INACTIVITY”
FROM V$SESSION;

Fast forward to 11gR2 and there must be a subjectively better way, right? Or, more importantly, one more elegant, now that I need to actually document this simple script for coworkers or other DBAs to use. The above script fragment is both embarrassing to me and it kicks in my OCD. So it must be fixed. After perfunctory web searching and some regex needling, I came up with this:

SELECT
REGEXP_SUBSTR(NUMTODSINTERVAL(last_call_et,‘SECOND’),’(0*)(\d{2,3} \d{2}:\d{2}:\d{2})’,1,1,‘i’,2) “INACTIVITY”
FROM V$SESSION;

I don’t know if this is the best solution, but much more elegant than the TRUNC/MOD one it replaces. I removed the sign, because a negative here would result in a time travel paradox, ending our universe as we know it. The first regex group globs as many leading zeros off of the day portion as it can, ultimately to be thrown away by REGEXP_SUBSTR. The second group first grabs the day, using only 2 or 3 digits, because I will never have a database up for more than three years. The last part of the second group just grabs two digits for each of hour, minute and second, in that order. I don’t need the fractional seconds for this, so they’re left off. The rest of the arguments for REGEXP_SUBSTR should be easy to glean from a doc page.

I really want your TO_CHAR/TIMESTAMP to work, but the dagblammed “day” is getting in the way of it being preferred over my regex. I still blame Oracle.

Thanks for the suggestion! But the compactness of my regex appeases my OCD more.
[B]

Rich

6

because I will never have a database up for more than three years

same here, but close to it

$ uptime

5:21pm up 984 day(s), 1:01, 1 user, load average: 0.21, 0.24, 0.24

cheers

Laurent

7

Nice!!! At my last job 8+ years ago, we hit 4 digits on a few servers, but never the databases. Even if they didn’t need patching/maintenance, they always seemed to suffer from software entropy.

I’m lucky to hit a year of uptime here, with more aggressive patching and and a growing business.

Rich

p.s. I did some quick perf testing of the three formatting methods. Your TO_CHAR/TIMESTAMP method was the fastest, with EXTRACT in at reasonably close second, and my REGEXP_SUBSTR a very distant third (3-5x the CPU). But took a 4-way cartesian join on V$SESSION to get enough elapsed time to make the testing meaningful, so I’ve got that going for me, which is nice… :slight_smile: