I'd like to store the time of day a report is to run. I'm considering using interval day(0) to second(0), since the only parts I want are the hour:minute. I am having trouble with the syntax of secifying the interval value:
DECLARE
intvl interval day(0) to second(0) := '05:30';
newdate date;
begin
select trunc(sysdate) + intvl into newdate from dual;
dbms_output.put_line('newdate is '||newdate);
end;
/
declare
*
ERROR at line 1:
ORA-01867: the interval is invalid
ORA-06512: at line 2


SQL}EVEDW@dev1:evedb2>declare
2 intvl interval day(0) to second(0) := 5hours 30minutes;
3 newdate date;
4 begin
5 select trunc(sysdate) + intvl into newdate from dual;
6 dbms_output.put_line('newdate is '||newdate);
7 end;
8 /
intvl interval day(0) to second(0) := 5hours 30minutes;
*
ERROR at line 2:
ORA-06550: line 2, column 40:
PLS-00103: Encountered the symbol "HOURS" when expecting one of the following:
* & = - + ; < / > at in is mod not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like between ||

I want to be able to maintain this starttime in an easily understood format like HH24:MI.

Thanks for your help. This is a super site.

two ways -- first, write a tiny function:

ops$tkyte@ORA920LAP> create or replace function hh24mi_to_interval( p_string in varchar2 ) return interval day to second
  2  as
  3  begin
  4          return to_dsinterval( '0 ' || p_string || ':00' );
  5  end;
  6  /

Function created.

<b>and use that:</b>

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> declare
  2          ival interval day(0) to second(0) := hh24mi_to_interval('05:30');
  3  begin
  4          dbms_output.put_line( ival );
  5  end;
  6  /
+0 05:30:00

PL/SQL procedure successfully completed.

<b>or, use "dual"</b>

ops$tkyte@ORA920LAP> declare
  2          ival interval day(0) to second(0);
  3  begin
  4          select interval '05:30' hour to minute into ival from dual;
  5          dbms_output.put_line( ival );
  6  end;
  7  /
+0 05:30:00

PL/SQL procedure successfully completed.


<b>since plsql won't take the hour to minute syntax...</b>
 

Thanks Tom.

Name Data Type
TS, TS1 TIMESTAMP
TSZ TIMESTAMP WITH TIME ZONE
TLZ TIMESTAMP WITH LOCAL TIME ZONE
IYM INTERVAL YEAR TO MONTH
IDS INTERVAL DAY TO SECOND

What operation we can perform on these datatypes. It is just confusing.

A. IDS* 2
B. TS + IYM
C. TS -TS1
D. IDS - TS
E. IDS + IYM

1 Datetime +/- Interval = Datetime
2 Datetime - Datetime = Interval
3 Interval +/- Interval = Interval
4 Interval * Number = Interval
5 Interval / Number = Interval

SQL> desc l
 Name                  Type 
 -------------------- ------------------------------
 USERNAME              VARCHAR2(10) 
 TIMEIN                TIMESTAMP(3)
 TIMEOUT               TIMESTAMP(3)
 YM                    INTERVAL YEAR(2) TO MONTH
 DS                    INTERVAL DAY(2) TO SECOND(6)

column name format a15 not works with desc?

1 
SQL> select timein - ym, timein + ym from l;
Ok
2
Ok 
but datetime + datetime not allowed

3
SQL> select ym - ds from l;
select ym - ds from l
          *
ERROR at line 1:
ORA-30081: invalid data type for datetime/interval arithmetic

SQL> 

4 ok
5 ok

I have tried to subtract interval - datetime and there will be an error.

Your comments. 

Hello Tom,

How can we use the aggregate function sum with the interval datatype?

SQL> create table int_table(a interval DAY(1) TO SECOND(2));

Table created

SQL> insert into int_table values (interval '3' hour);

1 row inserted

SQL> insert into int_table values (interval '1' hour);

1 row inserted

SQL> commit;

Commit complete

SQL> select sum(a) from int_table;

select sum(a) from int_table

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

SQL> 

Since we can ADD intervals, we should be able to sum() them.
 

Ooops, i guess i did not search well enough.

Thanks.

Tom, why my dbtimezone and sessiontimezone are different? How do I make the same? I am in CST. Thanks.

demo@PRACTICE> select dbtimezone from dual;

DBTIME
------
-07:00

demo@PRACTICE> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------
-06:00

(1) On my windows 2000 laptop, I looked in RtClick on MyComputer --> Advanced tab on System Properties ---> Environment Variables. I don't find any TZ variable. How do I set on windows? Thanks.

(2) Also, please see below. What do I see in hour 20 as I don't see 20 in current_timestamp?
Thanks.

demo@PRACTICE.US.ORACLE.COM> select extract( hour from current_timestamp ) from dual;

EXTRACT(HOURFROMCURRENT_TIMESTAMP)
----------------------------------
20

demo@PRACTICE.US.ORACLE.COM> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------------25-FEB-04 02.11.21.779000 PM -06:00

Tom, I checked the 'clock' thing on my laptop, the time zone is set properly (GMT -06:00) Central Time. However,

demo@PRACTICE> select dbtimezone, sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------------------------
-07:00 -06:00

Could you please help how I can change DBTIMEZONE to -06:00? Thanks.

Hi Tom,
The timestamp in email sent by UTL_SMTP is 5 hours early than the EST of USA. I check the following,

SQL> SELECT SESSIONTIMEZONE,DBTIMEZONE FROM DUAL;
SESSIONTIMEZONE    DBTIME
-05:00             -04:00

SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------
17-MAR-04 12.44.13.390000 PM -05:00

The emails will have the right timestamp if I do some change according to a input in the forum of meatlink.

ALTER SESSION SET TIME_ZONE='US/Eastern';

SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
------------------------------------------
17-MAR-04 12.40.45.934000 PM US/EASTERN

SQL> SELECT SESSIONTIMEZONE,DBTIMEZONE FROM DUAL;
SESSIONTIMEZONE    DBTIME
US/Eastern         -04:00

I don't know what's difference between US/Eastern and -05:00. I think it's just different return format of the time zone. What's the actual number of the time zone US/Eastern? Why does it solve the email problem?

According to Oracle Documenation about sessiontimezone, <Quote>
SESSIONTIMEZONE returns the value of the current session's time zone. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.
<Quote>


Thanks, Tom







 

Hi tom,
I put it like the following,

utl_smtp.data(SmtpConn, HtmlHead
||'Date: '|| to_char(current_timestamp, 'dd Mon yy hh24:mi:ss TZD')||crlf
|| 'From:' || inSender || crlf
|| 'Subject: ' || inSubject||crlf
|| 'To: ' || inRecip || crlf
|| 'Cc: ' || inCCRecip || crlf
|| 'Bcc: ' || inBCCRecip || crlf || crlf||
inMessage);

Thanks,

until you give a TimeZone Designator (tzd) you don't "have one".

consider (forget utl_smtp for a moment...)


ops$tkyte@ORA9IR2> SELECT SESSIONTIMEZONE,DBTIMEZONE FROM DUAL;
 
SESSIONTIMEZONE                                                             DBTIME
--------------------------------------------------------------------------- ------
-05:00                                                                      -07:00
 
ops$tkyte@ORA9IR2> select to_char(current_timestamp, 'dd Mon yy hh24:mi:ss TZD') from dual;
 
TO_CHAR(CURRENT_TIMESTAMP
-------------------------
17 Mar 04 18:25:49
 
ops$tkyte@ORA9IR2> ALTER SESSION SET TIME_ZONE='US/Eastern';
 
Session altered.
 
ops$tkyte@ORA9IR2> select to_char(current_timestamp, 'dd Mon yy hh24:mi:ss TZD') from dual;
 
TO_CHAR(CURRENT_TIMESTAMP
-------------------------
17 Mar 04 18:26:03 EST
 


So, you see there is no TZD in the first one, hence -- smtp just gets the date/time and assumes "GMT".  By giving a named timezone, you give smtp enough data to "know what time it is" 

In response to the question about what the difference is in these two timezones: utl_smtp does NOT handle -05:00 as a timezone- it'll strip it out. It must be -0500 (no colon). One of my guys just noticed this in one of our notification systems.

Tom

In my database, D_PRG is a table that has a timestamp column 'CREATED_DT'. The following query works fine for an interval literal. It returns number of days of 209.

SELECT EXTRACT(DAY FROM INTERVAL '209 00:18:30.885000' DAY(3) TO SECOND) FROM D_PRG

but the following when the interval is computed per row, syntax error occurs. I could not find the correct syntax.

SELECT EXTRACT(DAY FROM INTERVAL SYSTIMESTAMP - CREATED_DT DAY(3) TO SECOND) FROM D_PRG

Can we extract interval component from a dynamic interval like SYSTIMESTAMP - CREATED_DT.

Thank you

Tom K. -

Please let me save you the trouble...

Jo Sat, your first example succeeds because you're dealing with
the literal "209 00:18:30.885000", and the use of "interval" is
essentially casting that to an interval datatype. But when
you refer to "SYSTIMESTAMP - CREATED_DT", you already have an
interval datatype, and you don't need to apply the "interval"
cast.

Here's an example that uses a query similar to yours:

tgaines@CHEETAH>drop table dummy655;

Table dropped.

tgaines@CHEETAH>create table dummy655 (x timestamp);

Table created.

tgaines@CHEETAH>insert into dummy655 values (systimestamp);

1 row created.

tgaines@CHEETAH>

-- Go get a soda so that some time passes on the system clock ...

tgaines@CHEETAH>column a format 99999
tgaines@CHEETAH>column b format 99999
tgaines@CHEETAH>column c format 99999
tgaines@CHEETAH>SELECT EXTRACT(DAY FROM (SYSTIMESTAMP - x) DAY(3) TO SECOND) a,
2 EXTRACT(minute FROM (SYSTIMESTAMP - x) DAY(3) TO SECOND) b,
3 EXTRACT(DAY FROM INTERVAL '209 00:18:30.885000' DAY(3) TO SECOND) c
4 FROM dummy655;

A B C
------ ------ ------
0 4 209

1 row selected.


That's all there is to it!

Tom G.

From Metalink Note:227334.1,
<quote>Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE columns.

You can only change the database timezone if you have no TIMESTAMP WITH LOCAL TIMEZONE columns in the database.<quote>

I am little confused about the above statement. I thought TIMESTAMP WITH LOCAL TIME ZONE columns are used to retrieve information in the "client" session time_zone value. Is it not the time always stored in the database time zone? If so, why can't we change the db timezone when there already are columns defined with TIMESTAMP WITH LOCAL TIMEZONE? Thanks.

I dont really understand why Oracle provides INTERVAL as a datatype. I can understand the simplicity and elegance it adds to date arithmetic as an operator i.e. + interval '1' hour instead of + 1/(24*60) and stuff.

But why a datatype? Isnt it just a number at the end of the day?

Thanks

interval '1' year
interval '1' hour
interval '10:30' minute to second

it is an "interval", a duration, a period of time. it is not just a "number" besides, ANSI said we should have one. It is part of the spec

Forget ANSI, standards bodies dont always make sense, they try to make everyone happy! ;) I was just trying to understand the rationale and usage.

Right, but an interval pre-9i is nothing but the difference between 2 DATE columns.

So

interval '1' year = 1*365 = 365
interval '1' hour = 1/24 = 0.0416
interval '10:30' minute to second = 0.0072916

So what am I gaining by defining it as a INTERVAL data type instead of just a NUMBER?

[OK on the last one I cheated, I calculated it by doing

select sysdate - (sysdate - interval '10:30' minute to second) from dual , but I agree that it makes date arithmetic more elegant and readable]

We are not on the same page. You are just proving my point.

"
select x from t

0.0072916

ok, tell me -- what is that number? what meaning does it convey? can you look at it and say "oh 10 hours, 30 minutes -- got it, no problem"

Not if I do that, but if I do

select numtodsinterval(x,'day') from t

I would get +00 00:10:29.994240 and I can say "ah, 10 hours and almost 30 minutes"

See, I understand the conversion functions, INTERVAL arithmetic, all the numtodstinterval, to_dsinterval, etc functions. They are great.

But all of them can be used even if the interval is stored in a NUMBER column.

But that still doesnt answer the question as to why would I want to store the interval using a INTERVAL datatype and not just NUMBER?

Thanks

SQL> create table t(x interval day(1) to second);

Table created.

SQL> insert into t values (interval '1' day);

1 row created.

SQL> insert into t values (interval '10' day);
insert into t values (interval '10' day)
                      *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

SQL> insert into t values (interval '1' year);
insert into t values (interval '1' year)
                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

Shouldnt the second error message be the same? I am trying to stuff more than the column's datatype max precision allows. Why is the second insert saying it expects a NUMBER?

Thanks 

"Hope that tells you why you'd want an interval and not a number -- because a duration of time isn't a number. because the interval conveys meaning, because the interval is unambigous -- is "5" 5 days, 5 hours, 5 minutes, 5 seconds, 5 whatevers - an interval removes all potential ambiguity"

Duration of time is a number but just with units. Like so many other things.

If I have a EMP table with a numeric HEIGHT column, is it in centimeters, inches or millimeters? This is simply resolved by having documentation or naming the column appropriately (height_inches)

I see your point though, I guess Oracle added it because ANSI specifies it, whatever.

Thanks

Excelent Tom,

The below sql will give me time difference in seconds. How do we get the difference in mili seconds?

select
avg( extract( day from (systimestamp - (systimestamp - 1)) )*24*60*60 +
extract( hour from (systimestamp - (systimestamp - 1)) )*60*60 +
extract( minute from (systimestamp - (systimestamp - 1)) )*60 +
extract( second from (systimestamp - (systimestamp - 1)))
) avg_secs
from dual

it is already there.


ops$tkyte@ORA9IR2> create table t ( x timestamp, y timestamp );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( systimestamp , null );
 
1 row created.
 
ops$tkyte@ORA9IR2> exec dbms_lock.sleep(1)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> update t set y=systimestamp;
 
1 row updated.
 
ops$tkyte@ORA9IR2> select extract( second from (y-x) ) from t;
 
EXTRACT(SECONDFROM(Y-X))
------------------------
                1.017515
 

Is there any way to implement a constraint based on a from and to date that ensures no overlapping date ranges?

Happy New Year Tom

Unfortunately the link

</code> http://asktom.oracle.com/~tkyte/9iTrain/img12.html <code>

appears not to work - If the document is still around, I'd be interested.

Unbelieveably, we are just now migrating from 8i to 9i!

Is there a write up of how Oracle dates, intervals, and date arithmetic works?

This whole thing seems simple until you start really working with it.

For example, storing a time of day as an interval makes a lot of sense until you think about daylight savings time.

What happens when you spring forward or fall back if your times are stored as intervals?

How is 2:30am interpreted on the day that you have two 2:30 ams? On the day you don't have one at all?

If I add 3/24 to midnight on the day you fall back do I get 2:00am or 3:00am? What about if I add 1? Does that get the next day at midnight or the same day at 11:00pm?

Hi Tom,
I would like to delete the rows from a table
which have login details older than p_hrs where p_hrs
is the parameter passed to a procedure.

Will this PL/SQL statement work??

delete from trw_logger_dtl
where to_char(log_date,'hh') - p_hrs <= p_hrs


Please do reply.

Hi Tom,
Thanks for your reply.
log_date column is of timestamp datatype.

The procedure is intended to delete the rows
from the table at what ever time it is run
taking into consideration the current time.


I used this one in the WHERE clause:

to_char(log_date,'hh') <= to_char(systimestamp,'hh') - p_hrs

Is this correct??

Hi Kumar,

try

log_date <= systimestamp - numToDSinterval( p_hrs , 'HOUR' )

Thanks for the examples in this post. I have come up with a slightly less verbose method of calculation difference between two timestamps in seconds to microsecond accuracy

...
, duration_us = duration_us +
(TO_DATE(TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS')
-TO_DATE(TO_CHAR(last_start_dttm,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'))*86400
+TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'xFF'))
-TO_NUMBER(TO_CHAR(last_start_dttm,'xFF'))
...

Tom,
Assuming this is a bug, figured I'd let you have a peek and tell me I'm not nuts. When using date arithmetic with timestamps the result is supposed to be a string you can print, say with dbms_output. Well, look at the below PL/SQL block and results:

declare
t1 timestamp;
t2 timestamp;
begin
t1 := systimestamp;
for i in 1 .. 100000 loop
null;
end loop;
t2 := systimestamp;
dbms_output.put_line(systimestamp-t1);
dbms_output.put_line(systimestamp-(t1-4/24));
dbms_output.put_line((systimestamp+4/24)-t1);
dbms_output.put_line(t2-t1);
end;

-000000000 03:59:59.982762000
+000000000 00:00:00.988572000
+000000000 03:59:59.028977000
+000000000 00:00:00.017074000

Clearly the first and last statements should provide answers very close to 0. I happen to have a GMT offset of 4 hrs and the second and third statements were attempts to play around with the "bug". What is even more disturbing is that the second and third statements should be equivalent, yet even they return different answers. From the limited playing around I did, it appears to be when trying to use systimestamp in the arithmentic that poses a problem, but I also happen to have working code in an application that does that and works.

I'm sure you'll want to know what DB version - it is 9.2.0.7.0. Would be curious to see what kind of results you get on your end on different versions.

ops$tkyte@ORA9IR2> declare
  2     t1 timestamp;
  3     t2 timestamp;
  4  begin
  5     t1 := systimestamp;
  6     for i in 1 .. 100000 loop
  7        null;
  8     end loop;
  9     t2 := systimestamp;
 10     dbms_output.put_line(systimestamp-t1);
 11     dbms_output.put_line(systimestamp-(t1-4/24));
 12     dbms_output.put_line((systimestamp+4/24)-t1);
 13     dbms_output.put_line(t2-t1);
 14  end;
 15  /
+000000000 00:00:00.098071000
+000000000 04:00:01.068724000
+000000000 04:00:00.029489000
+000000000 00:00:00.097912000

PL/SQL procedure successfully completed.


what are all of your relevant TZ settings - all of them, OS level, db level, everything.

 

OK, at the risk of sounding a little ignorant (but what the heck, I might as well own up to it like Phil Mickelson and his club selection), I have to admit I need help in knowing what you want. When you say "all of the relevant tz settings" I need some help in determining what info to give you. I'm on a Sun Solaris box from an OS standpoint. Both the box (and subsequently the db) use GMT. What tz settings do you want from the database and where would I view those (v$parameter, etc.)

Tom, after some digging into what you asked for I see a problem, but am very confused. The OS TZ is GMT. However look at these:

sessiontimezone = -04:00
dbtimezone = +00:00

Granted, that sems odd, but it shouldn't cause problems with timestamp arithmetic. How does sessiontimezone get initialized? From my Windows client I can log in and do the following:

alter session set time_zone='+00:00';

which then makes the session and db time zones the same, and the script then returns the results you would expect. If I log onto the Unix box and then into SQL*Plus I get "+00:00" for both sessiontimezone and dbtimezone. This would seem to imply that the timestamp arithmetic being performed was somehow dependent on the client machine. Is this because it is a PL/SQL block and not a stored procedure? I'm just confused how the client I log in from affects the timestamp arithmetic?

Show me the way...

Well, when I'm on the Unix box it's GMT. When I'm on my PC it is "(GMT -05:00) Eastern Time (US & Canada)" with the checkbox for "Automatically adjust clock for daylight saving changes" checked. Does my sessiontimezone automatically get set based on my Windows settings, and if so what can I do about that?

All that being said I'm still really confused as to what calculation is being performed using the dbtimezone and what is being performed using the sessiontimezone. It would appear there is an inconsistency/bug wouldn't you agree?

The statement:

dbms_output.put_line(systimestamp-t1);

is giving an erroneous result when the sessiontimezone and dbtimezone are different. However, if I print the values of both they show the same thing (neglecting the couple millisecond difference). Hence, both of the following statements must be using the same timezone, whether it be db or session:

dbms_output.put_line(systimestamp);
dbms_output.put_line(t1);

as the result is:

21-JUN-06 02.10.55.333631000 PM +00:00
21-JUN-06 02.10.55.316711 PM

However, when I attempt to perform arithmetic, something goes wrong because I get:

-000000000 03:59:59.982625000

This leads me to believe that when performing the arithmetic operation, the PL/SQL engine is using the dbtimezone to evaluate systimestamp, but using the sessiontimezone for t1. I suppose it's possible that the dbms_output of t1 alone caused the database to implicitly convert it to the dbtimezone so what was displayed didn't really reflect the internal value of the variable. If that was the case the arithmetic would actually be correct, but that would mean that the statement "t1 := systimestamp;" was evaluated using the sessiontimezone yet systimestamp in a dbms_output is evaluated using the dbtimezone.

The more I think about it, that sounds pretty likely. Statements in the PL/SQL block use sessiontimezone, as they should by definition, but in some calls to the database, the argument is passed to the database and evaluated there using the dbtimezone. A further confusing example:

declare
t1 timestamp;
t2 timestamp;
x interval day(0) to second(0);
begin
t1 := systimestamp;
t2 := systimestamp;
x := systimestamp - t1;
dbms_output.put_line(x);
x := t2 - t1;
dbms_output.put_line(x);
end;

-0 04:00:00
+0 00:00:00

The results should be the same. This seems to support my theory (maybe) that the statements are being evaluated using the sessiontimezone, but the srithmetic operation is passed to the database and systimestamp gets evaluated there and is now 4 hours different than t1. If that is the case it's still a problem...

Are you serious? Regardless, I didn't expect you to do anything about it, just give me your insight. But I'm failing to see how this is mixing tz's. I gave you the following code:

declare
t1 timestamp;
t2 timestamp;
x interval day(0) to second(0);
begin
t1 := systimestamp;
t2 := systimestamp;
x := systimestamp - t1;
dbms_output.put_line(x);
x := t2 - t1;
dbms_output.put_line(x);
end;

-0 04:00:00
+0 00:00:00

Can you honestly tell me that "systimestamp - t1" and "t2 - t1" should return different answers (when t2=systimestamp)if the session and db time zones are different. I'm ok if you tell me "Yes, that's the desired behavior" if I could just understand why. What is being evaluated at the db level vs. session level? I just having a hard time reasoning my last example out.

Yes, I can make a change so my session time zone matches the db time zone, but I kind of liken that to someone changing code to resolve a bug without really knowing why they are making the change (other than someone told them to). Strictly from an education standpoint I'd love to have an explanation on that sample code of what is being done internally with each line of that last example to know where the difference comes from.

Thanks for all of your help as usual.

I had been ignorant of any issues with the interval datatype until I read "Build Custom Aggregate Functions" in July/August Oracle Magazine. It was there I discovered the standard aggregate functions don't support intervals, and I thought, "Gee, that's a bummer". The solution in the article is indicated by the title.

I'm impressed with the ease with which user defined aggregate functions can be built, but defining avg() for intervals is just the tip of the iceberg. What about min(), max(), sum(), stddev(), corr(), etc! I reasoned that it would be more pragmatic to simply convert the interval into a decimal number with implicit units so that I could use any of the numeric aggregate functions. Oops, Oracle didn't think to provide an easy way to convert interval to number (hence my discovery of this thread).

The solution at the top of this thread works great, but it's a bit ungainly. Not to worry, I'll just pop the logic into a package and provide functions to convert intervals to days, hours, minutes, or seconds. (Code at bottom).

But I want to test my results. What better way than to use the built in reciprocal numToDSInterval. Start with an interval, convert to decimal seconds with my function, and convert back to interval with numToDSInterval should cycle back to my start value. Everything seemed to be working great, although the 9th decimal value for seconds was off when my interval had days. No big deal.

SQL> select to_dsinterval('1 0:0:0.123456789') before,
  2  numtodsinterval(dsinterval.to_seconds(
  3    to_dsinterval('1 0:0:0.123456789')),'second') after
  4  from dual;

BEFORE
----------------------------------------------------------
AFTER
----------------------------------------------------------
+000000001 00:00:00.123456789
+000000001 00:00:00.123456788

But as I continued to increase the magnitude of the interval, I crossed a threshold where everything broke down completely.

SQL> select to_dsinterval('100000 0:0:0.123456789') before,
  2  numtodsinterval(dsinterval.to_seconds(
  3    to_dsinterval('100000 0:0:0.123456789')),'second') after
  4  from dual;

BEFORE
-----------------------------------------------------------
AFTER
-----------------------------------------------------------
+000100000 00:00:00.123456789
+000024855 03:14:07.147483647

Through trial and error I discovered that numToDSInterval is worthless when the integral portion of the 1st argument exceeds the limits of a signed 4 byte integer. OUCH! I haven't seen this bug reported anywhere, though I haven't looked too hard. (unless this is related to the buffer overrun problem, but that was reported as having to do with an excessively long string value in the 2nd argument).

OK, I can work around this. All I need to do is extend my package to support conversion of number back to interval. How hard can that be? (You’d think I’d have learned by now that nothing with Oracle intervals is as easy as it should be)

Well I quickly found a horrific PL*SQL bug concerning functions returning intervals: The compiler implicitly limits the function return precision to interval day(2) to second(6)! The following code demonstrates:

create or replace function return_interval( str varchar2 )
  return interval day to second
is
  i interval day(9) to second(9);
begin
  i := to_dsinterval(str);
  return i;
end;
/

SQL> select return_interval('99 23:59:59.123456') from dual;

RETURN_INTERVAL('9923:59:59.123456')
------------------------------------------------------------------
+000000099 23:59:59.123456000

SQL> select return_interval('99 23:59:59.123456789') from dual;

RETURN_INTERVAL('9923:59:59.123456789')
------------------------------------------------------------------
+000000099 23:59:59.123457000

SQL> select return_interval('100 0:0:0') from dual;
select return_interval('100 0:0:0') from dual
       *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at "SCOTT.RETURN_INTERVAL", line 8


I tried explicitly setting the precision of the return value in the function declaration, but of course that wouldn’t compile – parameters and return values must be unconstrained.

Well I was finally stumped. The best I could do was abandon the idea of returning an interval, and return a string formatted as an interval instead. I can then use Oracle’s to_dsinterval to convert my return value to the interval if necessary.

Eureka! I finally have a fully functioning convenient package for converting between numbers and intervals. I just have to be sure to remember that the FROM functions return a string, and not an interval.

The tests below demonstrate how I can convert without any loss of precision, even when approaching the max capacity of an interval. 

SQL> select to_dsinterval('999999999 23:59:59.123456789') test_days_input, to_dsinterval( dsinterval.from_days( dsinterval.to_days(
to_dsinterval('999999999 23:59:59.123456789') ) ) ) test_days_output from dual;

TEST_DAYS_INPUT
---------------------------------------------------------------------------
TEST_DAYS_OUTPUT
---------------------------------------------------------------------------
+999999999 23:59:59.123456789
+999999999 23:59:59.123456789


SQL> select to_dsinterval('999999999 23:59:59.123456789') test_hours_input, to_dsinterval( dsinterval.from_hours( dsinterval.to_hours
( to_dsinterval('999999999 23:59:59.123456789') ) ) ) test_hours_output from dual;

TEST_HOURS_INPUT
-----------------------------------------------------------------------
TEST_HOURS_OUTPUT
-----------------------------------------------------------------------
+999999999 23:59:59.123456789
+999999999 23:59:59.123456789


SQL> select to_dsinterval('999999999 23:59:59.123456789') test_minutes_input, to_dsinterval( dsinterval.from_minutes( dsinterval.to_min
utes( to_dsinterval('999999999 23:59:59.123456789') ) ) ) test_minutes_output from dual;

TEST_MINUTES_INPUT
-----------------------------------------------------------------------
TEST_MINUTES_OUTPUT
-----------------------------------------------------------------------
+999999999 23:59:59.123456789
+999999999 23:59:59.123456789


SQL> select to_dsinterval('999999999 23:59:59.123456789') test_seconds_input, to_dsinterval( dsinterval.from_seconds( dsinterval.to_sec
onds( to_dsinterval('999999999 23:59:59.123456789') ) ) ) test_seconds_output from dual;

TEST_SECONDS_INPUT
-----------------------------------------------------------------------
TEST_SECONDS_OUTPUT
-----------------------------------------------------------------------
+999999999 23:59:59.123456789
+999999999 23:59:59.123456789


And finally – as promised – here is the package source code:

create or replace package dsinterval
as

function to_days( i interval day to second )
  return number
;

function to_hours( i interval day to second )
  return number
;

function to_minutes( i interval day to second )
  return number
;

function to_seconds( i interval day to second )
  return number
;

function from_days( n number )
  return varchar2
;

function from_hours( n number )
  return varchar2
;

function from_minutes( n number )
  return varchar2
;

function from_seconds( n number )
  return varchar2
;

end;
/

create or replace package body dsinterval
as

function to_days( i interval day to second )
  return number
is
begin
  return extract( day from i )
       + extract( hour from i )/24
       + extract( minute from i )/24/60
       + extract( second from i )/24/60/60
  ;
end;

function to_hours( i interval day to second )
  return number
is
begin
  return to_days( i )*24;
end;

function to_minutes( i interval day to second )
  return number
is
begin
  return to_days( i )*24*60;
end;

function to_seconds( i interval day to second )
  return number
is
begin
  return to_days( i )*24*60*60;
end;

function from_days( n number )
  return varchar2
is
  d number := trunc(n);
  h number;
  m number;
  s number;
begin
  h := abs((n-d)*24);
  m := (h-trunc(h))*60;
  s := (m-trunc(m))*60;
  return to_char(d,'S099999999') || ' ' ||
         to_char(trunc(h),'FM09') || ':' ||
         to_char(trunc(m),'FM09') || ':' ||
         to_char(s,'FM09.999999990');
end;

function from_hours( n number )
  return varchar2
is
begin
  return from_days( n/24 );
end;

function from_minutes( n number )
  return varchar2
is
begin
  return from_days( n/24/60 );
end;

function from_seconds( n number )
  return varchar2
is
begin
  return from_days( n/24/60/60 );
end;

end;
/

 

Hi Tom,

I have not seen any reply on your site from 1/july/2006. I hope everything is fine at your end. 

I have following questions, 

1. I want to know difference between two timestamps in minutes and seconds ? Is there any other way than "extract " method shown by you ? 

2. How it is going take part of fraction of seconds while arriving exact difference 

for e.g.

18:21:32 SQL> select localtimestamp - (localtimestamp + 10 ) from dual;

LOCALTIMESTAMP-(LOCALTIMESTAMP+10)
-----------------------------------------------------------------------
-000000009 23:59:59.922523

I want this difference in minutes and seconds only. ( Preferably other than "extract", I am sure there must me some simple way to arrive at this solution )

thanks & regards
pjp






 

Hi Tom,

Why am I getting the below error?  Any workaround for this?

SQL> create table a(id number, ds interval day(2) to second(6), ym interval year(2) to month)
  2  /
Table created.

SQL> select id, nvl(ds,ym) from a;
select id, nvl(ds,ym) from a
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

 

hi Tom,

  1  declare
  2    d1 timestamp; --date;
  3    d2 timestamp; --date;
  4    ytd interval day(6) to second;
  5  begin
  6    d1 := '16-JUL-1972';
  7    d2 := sysdate;
  8    ytd := d2 - d1; 
  9    dbms_output.put_line (ytd);
 10* end;
SQL> /
+012543 21:15:08.000000

PL/SQL procedure successfully completed.

above, I get the interval days to second. How can I get something of the form 
34 years 4 months 2 days 21:15:08 and so on using interval data types? (The exact text format would be using string concatentaion of course - but can I get the values from interval datatypes using extract?).

When I try to use year to month interval , I get an error as below:

declare
  d1 timestamp; --date;
  d2 timestamp; --date;
  ytd interval year(4) to month; --day(6) to second;
begin
  d1 := '16-JUL-1972';
  d2 := sysdate;
  ytd := d2 - d1; 
  dbms_output.put_line (ytd);
end;
/

  ytd := d2 - d1; --trunc(d2, 'MM') - trunc(d1, 'MM');
         *
ERROR at line 8:
ORA-06550: line 8, column 10:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

Thanks,
Naresh 

If I need to query a table to retrieve only rows created in the last,say 5 minutes, every 5 minutes how could I do it if I have to factor in Daylight Savings Time?

Hi Tom,

How do we verify that a particular column of a table having DATE datatype falls between two variables V1 and V2 of TIMESTAMP datatype.

In other words how do we convert TIMESTAMP to DATE datatype.

Thanks

Hi Tom !
A continuation of the original question. You extract hour from a timestamp, and get a value.

When I extract hour from systimestamp like this:

select extract(timezone_hour from systimestamp) timezone_hour,
extract(hour from systimestamp) hour,
systimestamp, localtimestamp, current_timestamp
from dual;

I get this:

timezone_hour = 1,
hour = 15,
systimestamp = 14-01-2008 16:13:02,265524 +01:00,
localtimestamp = 14-01-2008 16:13:02,265546,
current_timestamp = 14-01-2008 16:13:02,265546 +01:00

Where I have thought I would have got a "16" for the HOUR field and not 15? Is this because I'm GMT+1 - or is there a way to get the 16, without me knowing in which time zone I'm in?

My session territory is Denmark and my session timezone is +1

best regards
Mette

Given a year to month interval, how do you find out the number of days? EXTRACT doesn't work. Is there a direct way or do I need to do something like:

SELECT (SYSDATE + INTERVAL '1' MONTH) - SYSDATE FROM DUAL

Never mind. It just occurred to me that that answer would change depending on which months. Sorry for asking a stupid question.

Tom,

Suppose we have a table with 4 date columns. I wanted to find out total time between two time intervals in a single SQL statement?

create table test_dt(dt1 date, dt2 date, d3 date, d4 date);

insert into test_dt values (to_date('10-JAN-2010 09:23:44'),to_date('10-JAN-2010 11:20:26'),to_date('11-FEB-2010 12:16:32'),to_date('11-FEB-2010 12:38:54'));

commit;

How do we find total time in hh:mi:ss between two intervals: (dt2-dt1) + (dt4-dt3)

Thanks!

ops$tkyte%ORA11GR2> select (cast(dt2 as timestamp)- cast(dt1 as timestamp))+(cast( d4 as timestamp )- cast( d3 as timestamp))
  2    from test_dt;

(CAST(DT2ASTIMESTAMP)-CAST(DT1ASTIMESTAMP))+(CAST(D4ASTIMESTAMP)-CAST(D3AST
---------------------------------------------------------------------------
+000000000 02:19:04.000000000

Date and timestamp arithmetic around the year 1 AD gives errors and inconsistent results. Am I missing something. Is this documented somewhere? Oracle will not allow year "0" but that is what it puts out after adding from dates in BC.

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 
SQL> 
SQL> select * from nls_session_parameters where parameter like '%FORMAT';
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                YYYY-MM-DD:HH24:MI:SS
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
 
SQL> 
SQL> alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS';
 
Session altered.
 
SQL> 
SQL> alter session set nls_timestamp_format='SYYYY-MM-DD HH24:MI:SS';
 
Session altered.
 
SQL> 
SQL> select to_timestamp('0001-12-31 23:59:59 BC','YYYY-MM-DD HH24:MI:SS AD')+numtodsinterval(1,'SECOND')  should_be_year_1 from dual;
 
SHOULD_BE_YEAR_1
---------------------------------------------------------------------------
 0000-01-01 00:00:00
 
SQL> 
SQL> column one_sec_before_ce format a30
SQL> column one_sec_into_ce      format a30
SQL> select timestamp'-0001-12-31 23:59:59' one_sec_before_ce,timestamp'-0001-12-31 23:59:59'+interval'00:00:01' hour to second as one_sec_into_ce from dual;
 
ONE_SEC_BEFORE_CE              ONE_SEC_INTO_CE
------------------------------ ------------------------------
-0001-12-31 23:59:59            0000-01-01 00:00:00
 
SQL> 
SQL> select to_date('0000-01-01','YYYY-MM-DD') from dual;
select to_date('0000-01-01','YYYY-MM-DD') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
 
 
SQL> 
SQL> -- this one is just CRAZY.  Output is year zero, month zero, day zero!
SQL> select to_char(to_date('0001-12-31 BC','YYYY-MM-DD BC') + 100,'YYYY-MM-DD BC') from dual;
 
TO_CHAR(TO_DA
-------------
0000-00-00 00

SELECT ROWNUM,TO_TIMESTAMP_TZ('2017-01-01 13:52:00.123456789 US/Eastern', 'YYYY-MM-DD HH24:MI:SS.FF TZR')
+ INTERVAL TO_CHAR(ROWNUM) DAY
FROM DUAL CONNECT BY ROWNUM<=365;

SQL> SELECT ROWNUM,TO_TIMESTAMP_TZ('2017-01-01 13:52:00.123456789 US/Eastern', 'YYYY-MM-DD HH24:MI:SS.FF TZR')
  2  + numtodsinterval(rownum,'DAY')
  3  FROM DUAL CONNECT BY ROWNUM<=365;

    ROWNUM TO_TIMESTAMP_TZ('2017-01-0113:52:00.123456789US/EASTERN','YYYY-MM-DDHH24:MI
---------- ---------------------------------------------------------------------------
         1 02-JAN-17 01.52.00.123456789 PM US/EASTERN
         2 03-JAN-17 01.52.00.123456789 PM US/EASTERN
         3 04-JAN-17 01.52.00.123456789 PM US/EASTERN
         4 05-JAN-17 01.52.00.123456789 PM US/EASTERN
         5 06-JAN-17 01.52.00.123456789 PM US/EASTERN
         6 07-JAN-17 01.52.00.123456789 PM US/EASTERN
...
...

Thanks.. Now I'm telling what I'm doing...

We've a need to get start and end day of DST in every part of the world (all timezones). I just did it using below query for US/Eastern

Now I want the same query to return star/end date of DST for all timezones specified in v$timezone_names table.

How could I do that? We've a global system and need to do this for every country...

Appreciate your help


SELECT
    *
FROM
    (
        SELECT
            EXTRACT(MONTH FROM t),
            EXTRACT(DAY FROM t),
            EXTRACT(TIMEZONE_HOUR FROM t),
            EXTRACT(TIMEZONE_REGION FROM t),
            EXTRACT(TIMEZONE_HOUR FROM t) - LAG(
                EXTRACT(TIMEZONE_HOUR FROM t),
                1,
                EXTRACT(TIMEZONE_HOUR FROM t)
            ) OVER(
                ORDER BY
                    EXTRACT(TIMEZONE_REGION FROM t),
                    EXTRACT(MONTH FROM t)
            ) AS diff,
            ( TO_CHAR(t,'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD') )
        FROM
            (
                SELECT
                    to_timestamp_tz('2017-01-01 2:00:00.123456789 US/Eastern','YYYY-MM-DD HH24:MI:SS.FF TZR') + numtodsinterval(ROWNUM,'DAY') t
                FROM
                    dual
                CONNECT BY
                    ROWNUM <= 365
            )
        ORDER BY t
    )
WHERE
    diff <> 0

        FROM
            (   SELECT  to_timestamp_tz('2017-01-01 2:00:00.123456789 '||tzname,'YYYY-MM-DD HH24:MI:SS.FF TZR') + numtodsinterval(ROWNUM,'DAY') t
                FROM  ( select 1 from dual CONNECT BY ROWNUM <= 365 ),
                      ( select tzname from V$TIMEZONE_NAMES) 
            )

Team,

why this "year" works but not the "month" - is it not possible to subtract a "month" from a timestamp datatype ?

rajesh@PDB1> select systimestamp - numtoyminterval(1,'year') from dual ;

SYSTIMESTAMP-NUMTOYMINTERVAL(1,'YEAR')
---------------------------------------------------------------------------
31-OCT-21 11.14.06.621356000 AM +00:00

rajesh@PDB1> select systimestamp - numtoyminterval(1,'month') from dual ;
select systimestamp - numtoyminterval(1,'month') from dual
                    *
ERROR at line 1:
ORA-01839: date not valid for month specified