I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.
i.e (col1 + col 2)/60 = 34.87
what I need to do is make sure that when the number reaches 60 it moves on to 35.
is there a prebuilt function that will round say the time of a sysdate up or down 5 mins? so i entered 5:32pm i would want it to round it down to 5:30pm
View 1 Replies View RelatedI currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .
I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:
case :blk.duration_type
when 'HRS' then
:blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration);
when 'MINS' then
:blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);
However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .
I am trying to covert a time shown as 001200 (12 hours) to 720 mins. I have tried the following but get errors when executing:
(MTTD)>=TO_DATE('000000','HH24:MI:SS').
I have also tried .
MTTD = to_date('000000','HH24:MI:SS') =trunc(sysdate,'MM')*24*60; and neither is working.
What am I doing incorrectly?
How to get Time Difference between two DateTime Columns in Oracle 10g ?
View 10 Replies View RelatedI have two varchar variable which has value like this
v_outpunch1:='17:50:00'
and v_Shifttime:='18:00:00'
this both time i am subtracting here and storing in another varchar variable which is like this.
v_EarlyLeaverstimeformat := LPAD((extract(hour from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(hour from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(minute from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(minute from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(second from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(second from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0');
it's not subtracting value correctly.
I had a database at microsoft access but i had move it to the oracle database.
When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:
oracle_date.PNG ( 74.61K )
Number of downloads: 4
But when i link it to microsft access. like i will control the oracle from access. the result is at below:
access_date.PNG ( 136.11K )
Number of downloads: 3
So, my problem is how to display the date and time fully in oracle?
I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.
select Date, Person, Language, Country
from TableATableA
Date Person Language Country
01/25/2013 James English
12/20/2012 James English
US
[code]....
I am not able to find any information regarding inserting data with timestamp format without putting the date.
This is what i want to do:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';
I create the column data type as timestamp with time zone. This is value i have inserted:
Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);
And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:
Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;
But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.
BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:
Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;
Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?
I have developed customer entry form, its working fine. I have 2 display items on form. One is country name & other is region, these are fetched based on country id LOV.
Now I want to display country name & region at time of navigation. I have written following code on WHEN_NEW_RECORD_INSTANCE of block.
******
DECLARE
CCNTRY_ID VARCHAR2(2);
CCNTRY_NAM VARCHAR2(25);
CREGION VARCHAR2(11);
begin
select cntry_nam,region into ccntry_nam,cregion from cntry where cntry_id=ccntry_id;
exception
when no_data_found then
message('NO DATA FOUND');
end;
**********
I always get message not data found on navigation. Problem is only with the display item other, values of database columns are being shown.
i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00
so in this it should return 360 minutes.
I need below proc like...
procedure p1
(
i_time_min number -- minutes to be substracted from timestamp
)
is
v_end_timeinstamp timestamp(6);
begin
[code]....
The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.
How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.
The sample data has been enclosed with it.
I have a date in_sdate as In parameter defaulted to sysdate. Basing on this in_Sdate I calculate my start and end dates as:
v_sdate TRUNC (in_sdate, 'MI') - 15 / 1440 ;
v_edate := TRUNC (in_sdate, 'MI');
My procedure is run for every 15 minutes. Now suppose if I am running for old dat, then I should get the difference of dates by taking
v_old_Date := v_edate - in_Sdate;
Divide this by 15 , round that value and loop to run the procedure for that n times. My doubt is when I am saying
v_old_date := v_edate - in_sdate ; I am getting expression is of wrong type. How can I take the difference of the dates and get the minutes from that ?
in our database 10.0.2.4 with RAC archive log generated each 4 min , did this increase the performance of database and how i can fix it
View 14 Replies View Relatedhow to get correct result for the time subtraction of minutes for the following query
select (to_date('14-06-2012 11:10:00','dd-mm-yyyy hh24:mi:ss') - to_date('14-06-2012 11:00:00','dd-mm-yyyy hh24:mi:ss'))*60*60 as
result from dual
RESULT
----------
25
i want the output as 10 minutes.
In one of my application oracle net connection is taking 30 to 40 ms(tnsping servicename),but in actual connection is always taking more than 45 ms, and my application requirement is below than 10 to 20 ms.
And can i use any other connection method like hostname, ezconnect etc.
I am working with a new client and am still waiting on access to systems so I'm a bit hampered in looking at details of the database and environment. The database is OLTP and typical response time for returning result sets is under 2 seconds, often less than 1 second.
A simple SELECT on a few columns of v$session_longops (no subqueries, group by, having, etc) ran for more than 5 minutes.
I want to ask whether I can create range partition of like 10 minutes? Like I have data of 1 hour and I want to make 10 partition?
View 12 Replies View RelatedI have a Windows 2003 VirtualBox instance, to which I assigned 3 out of 4 cores my laptop has. This is a demonstration environment for an Oracle vertical product. I got it from my colleagues. The OS boots without starting the DB services - I did this deliberatley while trying to figure out what is happening.
About 2 and half to 3 and a half minutes after the service is started the oracle.exe "latches onto a core and does not let go" (as best as I can describe what I see). With 3 cores I see 33%-34% cprocessor use in the task manager with oracle.exe doing all the using. Nothing else is started. There is no process of which I am aware which actually uses the DB. I only start the TNS listener and the database service.
Once I start it, the demonstration software uses the database extensively for complex queries. With one of the 3 cores 100% used by the oracle.exe I am running short on CPU at times, which makes the demonstratin seem slaggish, and queries take longer than is really acceptable (not surprising seeing oracle.exe is very busy doing I know not what).
i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.
can i get in this format?
i am seeing (LGWR switch) happening in my databsae alert log every 3-4 minutes. is that appropriate? if not, what measure can i take to reduce this?
View 1 Replies View RelatedI need the query to calculate minutes from difference of to number fields. Test case is as below.
DROP TABLE tmp;
CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......
CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32
Required output is
CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238
I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :
SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;
VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00
ALTHOUGH THERE IS TIME . Its not zero .
I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add
12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----
I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.
start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes
If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example
start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)
Today i noticed one problem with my database,my redologs switches in every 3mins,i also noticed there is no more transaction changes happening in database but still redo switches.
Fri Oct 05 06:10:05 2012
Thread 1 advanced to log sequence 79244
Current log# 2 seq# 79244 mem# 0: D:ORADATAORACIREDO02.LOG
Fri Oct 05 06:12:16 2012
Thread 1 advanced to log sequence 79245
Current log# 1 seq# 79245 mem# 0: D:ORADATAORACIREDO01.LOG
Fri Oct 05 06:14:28 2012
[code]......
why redo switch happening,any internal problem causes redo to switch .
I have installed Oracle 10g RAC crs and asm on CentOS release 5.4. When I am rebooting the nodes and starting crs manually All the services on both nodes starting successfully.
---------------------------------------------------------
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
[code].....
But After a Few time/min few services goes down automatically and its show something like
---------------------------------------------------------
[root@rac1 ~]# crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE UNKNOWN rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
[code].....
writing a script where there is a blocking lock for more than 30 minutes ?
View 2 Replies View RelatedI have oracle 11g r2, I have a table emp, where i have three columns emp_no,status and creation_date, creation date is a time stamp like '02/09/2012 17:59:47'.
I have to trigger an email if the status is not updated for 30 minutes.
DECLARE
CUSROR C_GET_DATA IS
/*SELECTING RECORDS*/
SELECT * FROM EMP WHERE creation_date BETWEEN CREATION_DATE AND CREATION_dATE+30 AND STATUS IS NULL;
[Code]....
in the above code "CREATION_DATE AND CREATION_dATE+30" will give 30 days, how can i select just 30 minutes and schedule it every 30 minutes, so that email is triggered for if status is not updated for more than 30 minutes.