RE: Date / Time
Title: RE: Date / Time Here is my attempt to display the difference between two dates in the format days:Hours:Minutes:seconds The two dates are dt1 and dt2 If dt1 is later than dt2 then result returned as negative. It should work for differences of +/- 99 days - to increase the range, change '09' at the end of the first line to the size desired. to_char(trunc(greatest(dt2,dt1) - least(dt2,dt1)) * sign(dt2 - dt1),'09') || ':' || to_char(to_date(1,'J') + (greatest(dt2,dt1) - least(dt2,dt1)) ,'HH24:MI:SS') Regards Garry -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED]] Sent: 10 July 2001 15:12 To: Multiple recipients of list ORACLE-L Subject: Re: Date / Time Sajid, Unfortunately, I ran into the same problem. I didn't find anything to do it for me, either, so I had to write the pl/sql code. It is very long and messy, but can be done. When I did it, I didn't even attempt the days notation. Here is the code that I wrote: date_diff := trans_cur.modified_date - last_date; date_diff_tot := trans_cur.modified_date - first_date; SELECT decode( trunc( date_diff * 24),0, to_char( trunc( date_diff * 1440), 'FM90') || ':' || to_char( round( date_diff * 86400) - trunc( date_diff * 1440) * 60,'FM00'), to_char( trunc( date_diff * 24),'FM90') || ':' || to_char( trunc( date_diff * 1440 - trunc( date_diff * 24) * 60),'FM00') || ':' || to_char( round( date_diff * 86400 - trunc( date_diff * 1440) * 60), 'FM00')), decode( trunc( date_diff_tot * 24), 0, to_char( trunc( date_diff_tot * 1440),'FM90') || ':' || to_char( round( date_diff_tot * 86400) - trunc( date_diff_tot * 1440) * 60,'FM00'), to_char( trunc( date_diff_tot * 24),'FM90') || ':' || to_char( trunc( date_diff_tot * 1440 - trunc( date_diff_tot * 24) * 60),'FM00') || ':' || to_char( round( date_diff_tot * 86400 - trunc( date_diff_tot * 1440) * 60), 'FM00')) INTO elapsed_1, elapsed_2 FROM dual; I hope this helps. Sajid Iqbal wrote: Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). All internet traffic to this site is automatically scanned for viruses and vandals.
Re: Date / Time
Sajid, Unfortunately, I ran into the same problem. I didn't find anything to do it for me, either, so I had to write the pl/sql code. It is very long and messy, but can be done. When I did it, I didn't even attempt the days notation. Here is the code that I wrote: date_diff := trans_cur.modified_date - last_date; date_diff_tot := trans_cur.modified_date - first_date; SELECT decode( trunc( date_diff * 24),0, to_char( trunc( date_diff * 1440), 'FM90') || ':' || to_char( round( date_diff * 86400) - trunc( date_diff * 1440) * 60,'FM00'), to_char( trunc( date_diff * 24),'FM90') || ':' || to_char( trunc( date_diff * 1440 - trunc( date_diff * 24) * 60),'FM00') || ':' || to_char( round( date_diff * 86400 - trunc( date_diff * 1440) * 60), 'FM00')), decode( trunc( date_diff_tot * 24), 0, to_char( trunc( date_diff_tot * 1440),'FM90') || ':' || to_char( round( date_diff_tot * 86400) - trunc( date_diff_tot * 1440) * 60,'FM00'), to_char( trunc( date_diff_tot * 24),'FM90') || ':' || to_char( trunc( date_diff_tot * 1440 - trunc( date_diff_tot * 24) * 60),'FM00') || ':' || to_char( round( date_diff_tot * 86400 - trunc( date_diff_tot * 1440) * 60), 'FM00')) INTO elapsed_1, elapsed_2 FROM dual; I hope this helps. Sajid Iqbal wrote: Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Date / Time
Sajid Iqbal wrote:
Hello All
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
Please CC any replies directly to me at [EMAIL PROTECTED]
Thanks in advance,
Saj.
--
Sajid Iqbal
Database Team Leader
Sajid,
The difference of two dates is a number of days. If you want to use a
function similar to the dates function, you have to treat the day and
fractional parts separately, eg
select trunc(date2 - date1) days,
to_char(trunc(sysdate) + (date2 - date1) - trunc(date2 - date1),
'HH24:MI')
This uses a trick, the fact that date + number is a date ('number' is
assumed to be a number of days) - so we can use 0:00 today as 'base' and
just display hours and minutes (and seconds if you need them).
--
Regards,
Stephane Faroult
Oriole Corporation
--
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Date / Time
SELECT 12.0194907 ,FLOOR((12.0194907*86400)/86400) || ':' || TO_CHAR(TO_DATE(ROUND(SUBSTR(12.0194907,INSTR(12.0194907,'.'))*86400),'s '),'HH24:MI:SS') FROM dual HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Date / Time
On Tue, Jul 10, 2001 at 05:26:09AM -0800, Sajid Iqbal wrote:
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
using a as your value (cos it's shorter to write)
trunc(a)||':'||to_char(trunc(sysdate)+a-trunc(a), 'HH24:MI:SS')
You could get away with using
to_char(a+to_date('31-Dec-00'), 'DD:HH24:MI:SS')
if you can ensure that 1 = a 32
--
T.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tommy Wareing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Date / Time
Hi
The script below may help you - it will display different components in separate
columns, modify it with your two dates and proper table name and remove what you
don't need
select sysdate,
sysdate - to_date('0307200100', 'DDMMHH24MISS') diffr,
trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')) days,
trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'), 1) *
24) hrs,
trunc(mod(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24, 1) * 60) min,
round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60) sec,
to_char(trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')))
|| ' ' ||
lpad(trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24), 2, '0') || ':' ||
lpad(trunc(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60), 2, '0') || ':' ||
lpad(round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60), 2, '0') Difference
from dual
HTH
Witold
Sajid Iqbal wrote:
Hello All
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
Please CC any replies directly to me at [EMAIL PROTECTED]
Thanks in advance,
Saj.
--
Sajid Iqbal
Database Team Leader
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Date / Time
Sajid: Try this piece. I use a version of it for my uptime.sql script: TO_CHAR(TRUNC(date1 - date2)) || ' day(s), ' || TO_CHAR(TRUNC(MOD(date1 - date2 - 1, 1) * 24)) || ' hour(s), ' || TO_CHAR(TRUNC(((MOD(date1 - date2 - 1, 1) * 24) - (TRUNC(MOD(date1 - date2 - 1, 1) * 24))) * 60)) || ' minute(s) and ' || TO_CHAR(ROUND(MOD(((MOD(date1 - date2 - 1, 1) * 24) - (TRUNC(MOD(date1 - date2 - 1, 1) * 24))) * 60, 1) * 60, 1)) || ' seconds.' -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio --- Original Message --- From: Sajid Iqbal [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 7/10/01 9:26:09 AM Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: date+time in milliseconds
Hi Raj, I never heard such time Date can handle century + year + month + day + hour + minute + second but not milliseconds Sinardy -Original Message- Sent: Tuesday, 8 May 2001 1:14 PM To: LazyDBA mailing list can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj _ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com Think you know someone who can answer the above question? Forward it to them! To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: date+time in milliseconds
You can store the whole thing as a string in one column or store as two columns: date + milliseconds. The Oracle DATE type does not support time granularity below one second. We have used both methods here - just depends on how you want to use the data later. Ron Morton Database Architect / Administrator Union Switch Signal Inc [EMAIL PROTECTED] -Original Message- From: Sinardy Xing [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 2:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: date+time in milliseconds Hi Raj, I never heard such time Date can handle century + year + month + day + hour + minute + second but not milliseconds Sinardy -Original Message- Sent: Tuesday, 8 May 2001 1:14 PM To: LazyDBA mailing list can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj _ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com Think you know someone who can answer the above question? Forward it to them! To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morton, Ronald D INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: date+time in milliseconds
If you get milliseconds in the data and want to keep it, you have to store it in a field different than date, If you don't need to keep the milliseconds, you can use different functions to convert the data. If you need to get the time in milliseconds, you can write a Java stored procedure. You should in fact find the code in the list archives. In either case more details would help someone to help you Witold -Original Message- can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: date time
I figure it out. It's based on Geogrian date. Just add it to "Jan-1-1970'
then use TO_CHAR to convert it
From: [EMAIL PROTECTED]
To: mai huynh [EMAIL PROTECTED]
CC: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: date time
Date: Sun, 11 Mar 2001 12:35:35 -0800 (PST)
On Sat, 10 Mar 2001, mai huynh wrote:
does the date time data in Oracle store in long number? From the
application
interface, we enter data in format like '01/02/2001 10:30:00 PM' , when
I
run the query, I get the result in long number as '915134070' . When I
compare it to SYSDATE ('SELECT * FROM Mytable WHERE entry_date
SYSDATE-60'), I get the error inconsistent data type. How can I compare
the
field 'entry_date' to the last 60 days? Thanks
You will need to include some code to reproduce this.
Personally, I don't know how to make this happen.
Jared
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mai huynh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
