RE: subtract minute from date/time
I'm glad its not just me thats having a bad morning making the numbers work... My understanding of it goes something like this: When working with date/times, oracle considers 1 to mean "One day". There are 24 hours in every day, so to get the figure for one hour you use 1/24 eg sysdate - 1/24 would give you the time one hour ago. There are 60 minutes in each hour, so one minute is 1/(24*60) Which is 24 hours times 60 minutes Similarly, if you wanted seconds there are 60 in each minute, so 1 second is 1/(24*60*60) Thats one day divided by (number of hours in a day * number of minutes in an hour * number of seconds in a minute) You can probably do the same thing with decimals, but the numbers are messy. If Oracle handled anything smaller than a second, we'd probably start seeing issues with rounding errors... Hope thats clear and I haven't made any obvious mistakes. Simon Anderson -Original Message- Sent: Thursday, April 11, 2002 14:08 To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- -- 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: subtract minute from date/time
Sorry this is one second :-) "Jack van Zanen" <[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Re: subtract minute from date/time 11-04-2002 13:23 Please respond to ORACLE-L date-1/(24*60*60) Jack John Dunn co.uk> cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: subtract minute from date/time [EMAIL PROTECTED] 11-04-2002 13:08 Please respond to ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy o
Re: subtract minute from date/time
Try this query: select to_char (sysdate - (1/(24*60) * 5),'HH24:MI:SS') from dual; ^ this subtracts 5 mins from current date time. On Thu, 11 Apr 2002, John Dunn wrote: > > > I want to subtract a minute from a date/time > > How can I do this? > > John > > -- Volkan YILDIRIM Ankara Patent Bureau Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Volkan YILDIRIM 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: subtract minute from date/time
To subtract a minute you can do it this way select to_char(sysdate,'DD-MON- HH24:mi:ss') , to_char((sysdate - 1/(24*60)),'DD-MON- HH24:mi:ss') from dual; Hope this answers your query John Dunn <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 04/11/02 04:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: subtract minute from date/time I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: subtract minute from date/time
date_fld - 1/1440 Iain Nicoll Test and Release De-Regulated Services Internal : 700 2331 External : 0141 568 2331 -Original Message- Sent: Thursday, April 11, 2002 12:08 PM To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: Nicoll, Iain (Calanais) 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: subtract minute from date/time
select to_number(to_char(sysdate, 'MI')) from dual; JP On Thu 11. April 2002 13:08, you wrote: > I want to subtract a minute from a date/time > > How can I do this? > > John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: subtract minute from date/time
I know that if you have a date field then you can get the hour using the formula /60/60/24 thus to get the minutes will be /60/60 -Original Message- Sent: Thursday, April 11, 2002 14:08 To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: Hatzistavrou Giannis 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: subtract minute from date/time
date-1/(24*60*60) Jack John Dunn co.uk> cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: subtract minute from date/time [EMAIL PROTECTED] 11-04-2002 13:08 Please respond to ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -
RE: subtract minute from date/time
The only way Oracle understands date computations is through operations on a number of days - or fractions of days, assumed to be plain 24 hour days of 3600 second hours (no 23h 56mn there). To remove a minute, you take a date and substract the fraction of day equal to a minute - eg 1 / (24 * 60). >- Original Message - >From: John Dunn <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Thu, 11 Apr 2002 03:08:18 > >I want to subtract a minute from a date/time > >How can I do this? > >John > >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.com >-- >Author: John Dunn > 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). >--- >- Stephane Faroult Oriole Corporation Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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).
subtract minute from date/time
I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: Append date/time stamp to logfile name (NT 4.0)
Hello, Not sure if this is what you're looking for but you can use the following in a batch file: for /f "tokens=2-4 delims=-/ " %%i in ('date/t') do set filedate=%%k%%j%%i for /f "tokens=1-2 delims=: " %%i in ('time/t') do set filetime=%%i%%j ren logfile.log %filedate%%filetime%.txt This assumes your NT date format is "-MM-dd" or "/MM/dd"and your time format is "HH:mm:ss" or "H:mm:ss" both of which are configurable in CONTROL PANEL --> REGIONAL SETTINGS. HTH -- Chris J. Guidry P.Eng. ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] > -Original Message- > From: Speaks, Chuck W. [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, August 23, 2001 12:06 PM > To: Multiple recipients of list ORACLE-L > Subject: Append date/time stamp to logfile name (NT 4.0) > > OS: NT 4.0 > > I have scheduled a query against a database that pipes results to a log > file. > > Ex. Select * from any_table; >logfile.txt > > Since I need a trend on this query I need to keep the logfile names > unique. > Best case scenario is to append a date/time stamp to the logfile name as > in > 230820011200.txt (daymonthyeartime). Does anyone know how to do this with > NT scripting? Any help would be greatly appreciated. > > Thanks, > > Chuck Speaks > Database Administrator > Lithonia Lighting > 770-860-3450 > http://www.lithonia.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Speaks, Chuck W. > 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: Guidry, Chris 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: Append date/time stamp to logfile name (NT 4.0)
Chuck, It's doable, and I've seen scripts on the net that do it, but it's a bit cumbersome. However, you could also do it in sqlplus, using spool, where the name of the spool file is taken from a new_value of a date query, i.e., col x new_value fn select to_char (sysdate, 'DDMMHHMI') || '.txt' x from dual; spool &fn hth, yosi "Speaks, Chuck W." wrote: > OS: NT 4.0 > > I have scheduled a query against a database that pipes results to a log > file. > > Ex. Select * from any_table; >logfile.txt > > Since I need a trend on this query I need to keep the logfile names unique. > Best case scenario is to append a date/time stamp to the logfile name as in > 230820011200.txt (daymonthyeartime). Does anyone know how to do this with > NT scripting? Any help would be greatly appreciated. > > Thanks, > > Chuck Speaks > Database Administrator > Lithonia Lighting > 770-860-3450 > http://www.lithonia.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Speaks, Chuck W. > 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). -- Thanks, Yosi - Yosi Greenfield Oracle Certified DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yosi Greenfield 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).
Append date/time stamp to logfile name (NT 4.0)
OS: NT 4.0 I have scheduled a query against a database that pipes results to a log file. Ex. Select * from any_table; >logfile.txt Since I need a trend on this query I need to keep the logfile names unique. Best case scenario is to append a date/time stamp to the logfile name as in 230820011200.txt (daymonthyeartime). Does anyone know how to do this with NT scripting? Any help would be greatly appreciated. Thanks, Chuck Speaks Database Administrator Lithonia Lighting 770-860-3450 http://www.lithonia.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Speaks, Chuck W. 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
Title: RE: Date / Time Here is my attempt to display the difference between two dates in the format ::: 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: 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
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
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
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
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
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).
Date / Time
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).
RE: date+time in milliseconds
I heard Oracle 9i will have a supported date with milliseconds. Oracle 8i already knows it I think but it's not supported or public. Tamas Szecsy -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 2001.05.08. 3:07 PM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy Tamas 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 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
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
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).
date time
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 _ 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).