Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Carol.

Asked: June 27, 2003 - 5:36 pm UTC

Answered by: Tom Kyte - Last updated: October 06, 2017 - 6:34 am UTC

Category: Database - Version: 9i

Viewed 50K+ times! This question is

You Asked

I have the following query where TS_END and TS_START are DATE columns. I used to run this query to give me the average response time in seconds:

SELECT COUNT(*) system_process_count,
TRUNC(AVG(lgrt.ts_end - lgrt.ts_start)*24*60*60, 1)
FROM MD_LOG_RESPONSE_TIMES lgrt
WHERE lgrt.ts_start IS NOT NULL
AND lgrt.ts_end IS NOT NULL ;


TS_END and TS_START were just changed to TIMESTAMP columns so now my query generates an error. When 2 TIMESTAMPs are subtracted you get an INTERVAL. I can't figure out how to convert an interval to the number of seconds. Help!

Thank you.

and we said...

Either of:

ops$tkyte@ORA920> select avg( extract( day from (x-y) )*24*60*60+
2 extract( hour from (x-y) )*60*60+
3 extract( minute from (x-y) )*60+
4 extract( second from (x-y)) ) avg_secs from t;

AVG_SECS
----------
-1657063

ops$tkyte@ORA920>
ops$tkyte@ORA920> select numtodsinterval(
2 avg( extract( day from (x-y) )*24*60*60+
3 extract( hour from (x-y) )*60*60+
4 extract( minute from (x-y) )*60+
5 extract( second from (x-y)) ), 'second') avg_int from t;

AVG_INT
---------------------------------------------------------------------------
-000000019 04:17:43.000000000



will work.

and you rated our response

  (49 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Using stored interval to compute new date

July 16, 2003 - 1:10 pm UTC

Reviewer: nolan tucker from Harrisburg, PA USA

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.

Tom Kyte

Followup  

July 16, 2003 - 4:12 pm UTC

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>
 

July 17, 2003 - 7:21 am UTC

Reviewer: Nolan Tucker

Thanks Tom.

Timestamp and Interval

July 25, 2003 - 2:08 pm UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan

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

Tom Kyte

Followup  

July 25, 2003 - 4:58 pm UTC

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



My test

July 26, 2003 - 4:25 am UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan

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. 

Tom Kyte

Followup  

July 26, 2003 - 12:52 pm UTC



set linesize works with desc


2) what does it means ""add jan 1'st 2004 to july 22cnd 2003"???? no, adding datetimes doesn't make sense

3) they were at different scales. they are different types of intervals.



Tell me -- what would "8 hours - january 1st 2003" be exactly? again, like date+date it makes *no* sense

sum() with Interval

October 03, 2003 - 12:31 pm UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada

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.
 

Tom Kyte

Followup  

October 03, 2003 - 1:33 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5269744394385 <code>

Oops..

October 03, 2003 - 3:15 pm UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada.

Ooops, i guess i did not search well enough.

Thanks.



timestamp question

February 25, 2004 - 1:51 pm UTC

Reviewer: reader

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


Tom Kyte

Followup  

February 25, 2004 - 2:48 pm UTC

the dbtimezone is set for your server -- check the TZ environment variable if you are on unix (or use the pretty gui to fix it on windows) to make sure it is set right. CST would be -6:00 so it looks like your pc is set right but your server is not.



Timestamp question cont....

February 25, 2004 - 3:12 pm UTC

Reviewer: reader

(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 Kyte

Followup  

February 25, 2004 - 5:20 pm UTC

1) its in the clock utility thingy, usually on the lower right hand side of your screen.



2) in gmt, it is 20.

2pm is 14, plus 6 is 20.


Purpose EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.

timestamp cont

February 25, 2004 - 5:53 pm UTC

Reviewer: reader

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.

Tom Kyte

Followup  

February 25, 2004 - 7:30 pm UTC

see the ALTER DATABASE command, someone must have set it at database creation time.

read the ALTER DATABASE command -- it has important information -- eg: it does not change existing data, it requires a restart to take effect.

timestamp in email sent by UTL_SMTP is wrong

March 17, 2004 - 12:51 pm UTC

Reviewer: A Reader from USA

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







 

Tom Kyte

Followup  

March 17, 2004 - 3:23 pm UTC

how are you getting the "date" into the email -- are you putting it there yourself.

Timestamp is wrong

March 17, 2004 - 5:03 pm UTC

Reviewer: A Reader from USA

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,



Tom Kyte

Followup  

March 17, 2004 - 6:26 pm UTC

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" 

Timezone- eastern/us vs. -5:00

April 06, 2004 - 1:39 pm UTC

Reviewer: Patrick Dickey from Denver, CO USA

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 Kyte

Followup  

April 07, 2004 - 8:44 am UTC

are you sure that's utl_smtp -- it is just writing to the smtp server there. try it in TELNET and see if it is a function of your smtp server -- utl_smtp isn't really looking at anything

doesn't work in oracle 8

June 07, 2004 - 4:25 am UTC

Reviewer: A reader


Tom Kyte

Followup  

June 07, 2004 - 8:31 am UTC

no kidding -- many things don't work in version 8. nor in version 7 or version 6.....

especially when talking about new 9i features.

Extract() on timestamp data type

June 18, 2004 - 3:51 pm UTC

Reviewer: Jo Sat from VA

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 Kyte

Followup  

June 18, 2004 - 4:33 pm UTC

give me a fully, yet concise, test case to work with including "create table", 'Insert into table" and "select from table"

Just a slight error in your query...

June 18, 2004 - 5:16 pm UTC

Reviewer: Tom G. from Boulder, CO USA

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.


Tom Kyte

Followup  

June 18, 2004 - 6:08 pm UTC

thanks! (i've taken to scanning things that don't have examples and just asking for the example -- in order to economize on time. i find 9999 times out of 10,000 -- I find my own mistaken assumption(s) during the creation of such an example...)

timestamp

August 12, 2004 - 7:09 pm UTC

Reviewer: Robert

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.

Tom Kyte

Followup  

August 13, 2004 - 9:44 am UTC

it is because timestamp with local timezone is stored in the databases timezone that you cannot change the timezone of the database after using it -- it is a "safety measure". Changing the timezone of the database would *change* the data stored therein implicitly.

September 15, 2004 - 3:22 pm UTC

Reviewer: michael mischke


September 15, 2004 - 3:22 pm UTC

Reviewer: michael mischke from Berlin, Germany


Why datatype?

November 01, 2004 - 7:37 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

November 02, 2004 - 7:15 am UTC

No, it is not just a "number".


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

November 02, 2004 - 10:14 am UTC

Reviewer: A reader

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]

Tom Kyte

Followup  

November 02, 2004 - 10:53 am UTC

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"

standards bodies are extremely important. wish more companies followed them actually (hint -- i don't run a certain OS cause if I did, i could only run that OS due to all of its 'well the standards didn't quite work for us' thinking)

November 02, 2004 - 11:32 am UTC

Reviewer: A reader

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

Tom Kyte

Followup  

November 03, 2004 - 5:03 am UTC

so? how does that prove your point.

Actually, in that column X I store a number that is the number of blue moons between events -- only you mistakenly interpreted it as hours minutes.


there, that proves my point, you misinterpreted my number.

"42"

what is that?

interval 42 days

Ahh, got that, know what that is.


Do you store dates in strings? in numbers? (hope not, really really utterly bad and horrible practice - kills the optimizer too). No, you store them in dates.

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.



Inconsistent

November 02, 2004 - 5:16 pm UTC

Reviewer: A reader

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 

Tom Kyte

Followup  

November 03, 2004 - 6:40 am UTC

looks like a misleading error message, i agree. you are trying to put an interval year into an interval day type -- it should say expected interval got interval actually -- since it doesn't say what type of interval...

these sorts of things are best handled via support, they never get fixed unless someone points them out and they get logged.

November 03, 2004 - 8:52 pm UTC

Reviewer: A reader

"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

Time Difference in Mili Seconds

November 30, 2004 - 7:24 am UTC

Reviewer: Raj from India

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

Tom Kyte

Followup  

November 30, 2004 - 8:22 am UTC

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
 

Date Range Constraint

August 18, 2005 - 3:56 pm UTC

Reviewer: Yuan from Newark, NJ USA

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

Tom Kyte

Followup  

August 18, 2005 - 5:01 pm UTC

read
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:474221407101 <code>


Broken link

December 30, 2005 - 5:51 am UTC

Reviewer: Paul from UK

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!

Tom Kyte

Followup  

December 30, 2005 - 9:38 am UTC

Ok, I restored that - it is back

Is there a write up?

December 30, 2005 - 11:32 pm UTC

Reviewer: Michael Friedma from Shenzhen, China

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?

Tom Kyte

Followup  

December 31, 2005 - 10:57 am UTC


2:30am is ambigous

2:30am EDT and 2:30am EST are not ambigous

you need to keep the timezone if you use the timezone.

You have to look at the timezone in addition to the time component to see what time it is. It doesn't make sense to say "how is 2:30am interpreted" - you didn't give us the timezone. If there is no timezone, it'll be just "2:30am".

help

March 14, 2006 - 3:32 am UTC

Reviewer: Kumar from Pune,India

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.

Tom Kyte

Followup  

March 14, 2006 - 10:51 am UTC


it'll work in that it'll delete stuff. but probably not the stuff you "meant" to delete.

look at your where clause, how is it different from

where to_char(log_date,'hh') <= 2 * p_hrs


if you want to get rid of everything more than "p_hrs" old, you would simply:

where log_date <= sysdate- 1/24 * p_hrs


take the current time (sysdate), subtract p_hrs HOURS from it, find all records such that log_date is less than or equal to that.



ok

March 14, 2006 - 12:40 pm UTC

Reviewer: Kumar from Pune,India

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??


Tom Kyte

Followup  

March 15, 2006 - 9:21 am UTC

umm, no - as I said before, no it is not.


I gave you the answer already right above.

use numtodsinterval( rownum , 'HOUR' )

March 14, 2006 - 3:19 pm UTC

Reviewer: pasko from HH,Germany

Hi Kumar,

try

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

Difference between TIMESTAMPs

June 12, 2006 - 8:11 am UTC

Reviewer: David from UK

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'))
...

Timestamp arithmetic

June 19, 2006 - 2:06 pm UTC

Reviewer: Chris from Louisville, KY USA

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.

Tom Kyte

Followup  

June 19, 2006 - 6:48 pm UTC

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.

 

settings

June 19, 2006 - 9:55 pm UTC

Reviewer: Chris from Louisville, KY USA

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 Kyte

Followup  

June 20, 2006 - 9:39 am UTC

echo $TZ

dbtimezone sessiontimezone

are you SURE the db uses that timezone, can you verify that please (depending on OS release, you can get the TZ environment from a running process even using "ps" and dumping the environment. If someone set the TZ and then started the database for example...)

TZ

June 20, 2006 - 1:32 pm UTC

Reviewer: Chris from Lousiville, KY USA

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...

Tom Kyte

Followup  

June 21, 2006 - 9:35 am UTC

what is the TZ of your client....

TZ

June 21, 2006 - 10:33 am UTC

Reviewer: Chris from Louisville, KY USA

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...


Tom Kyte

Followup  

June 22, 2006 - 11:26 am UTC

... and if so what can I do about that? ....

logon trigger, application changes it, login.sql for sqlplus to set the session tz to the database tz.

you are mixing database timezone stuff with local timezone stuff. It is the way TZ's work.

Seriously?

June 23, 2006 - 8:19 am UTC

Reviewer: Chris from Louisville, KY USA

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.

Tom Kyte

Followup  

June 23, 2006 - 10:20 am UTC

if you believe it to be wrong, please contact support.

your session variables (t1, t2) versus the database function systimestamp - maybe you wanted to use localtimestamp?

New interval bugs? and a package work around

June 23, 2006 - 12:22 pm UTC

Reviewer: Dave Benham from Baltimore, MD

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;
/

 

two timestamps duration in minutes / hours

July 06, 2006 - 9:00 am UTC

Reviewer: Parag J Patankar from India

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






 

Tom Kyte

Followup  

July 08, 2006 - 10:12 am UTC

just a short vacation...


extract is the way to approach this.

Strange error with NVL

September 28, 2006 - 12:13 pm UTC

Reviewer: Kamini from INDIA

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

 

Tom Kyte

Followup  

September 29, 2006 - 7:48 am UTC

you are trying to return a YM interval for a DS interval - that cannot work.

year month is not convertable to day second.


tell me, how many days are in 1 year 2 months.

whatever you answer, you are wrong.

if you pick a non-leap year, I'll pick a leap year.
if you pick two months of jan/feb I'll pick mar/apr.

How to get the difference between 2 dates in terms of both YeartoMonth and daytoSecond

November 18, 2006 - 3:32 pm UTC

Reviewer: Naresh from Cyprus

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 

January 11, 2007 - 6:23 pm UTC

Reviewer: A reader

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?

Convert a timestamp to a date

January 25, 2007 - 9:52 am UTC

Reviewer: Hitesh Bajaj from INDIA

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

Extract hour from timestamp

January 14, 2008 - 10:23 am UTC

Reviewer: Mette Stephansen from DK

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
Tom Kyte

Followup  

January 14, 2008 - 3:55 pm UTC

use a localtimestamp call

Number of days in YtoM Interval

January 20, 2009 - 3:02 pm UTC

Reviewer: Yuan from Monmouth Junction, NJ USA

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
Tom Kyte

Followup  

January 20, 2009 - 3:42 pm UTC

Trick question

It depends doesn't it. What if the date you add the interval to is feb? That has 28 or 29 days....


You would need to take a date, add the interval to it, and then yes - subtract the original date again.

1 year and 1 month could be a variety of answers due to leap years, 30 day months, 31 day months and so on.

Number of days in YtoM Interval

January 20, 2009 - 3:04 pm UTC

Reviewer: Yuan from Monmouth Junction, NJ USA

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

Followup  

January 20, 2009 - 3:43 pm UTC

I should have read forward :)

no worries...

Total time between two time intervals

September 26, 2011 - 3:35 pm UTC

Reviewer: Ravi B from CA

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!

Tom Kyte

Followup  

September 27, 2011 - 8:17 am UTC

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

December 14, 2012 - 6:35 pm UTC

Reviewer: matt from CA USA

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

How can I pass Internal dynamically in the SQL Query

September 24, 2017 - 10:38 pm UTC

Reviewer: A reader

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;
Connor McDonald

Followup  

September 25, 2017 - 3:50 am UTC

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
...
...


September 25, 2017 - 5:46 pm UTC

Reviewer: A reader

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

Connor McDonald

Followup  

October 06, 2017 - 6:34 am UTC

I'm not entirely sure what requirement you are satisfying with that query, but in any event, to abstract it out to all timezones you can do:

        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) 
            )



And you'll need to adjust your LAG accordingly.