How To Round Time Up Or Down By 5 Minutes

Nov 29, 2006

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


ADVERTISEMENT

SQL & PL/SQL :: Time Calculation - Make Varchar To Timestamps But Caught With Round Up / Down?

Apr 12, 2011

calculating values of A & B

(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)

i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)

Formula ->

A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]

where

A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"

Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)

View 10 Replies View Related

How To Display Time In Minutes

Oct 15, 2012

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.

View 2 Replies View Related

Forms :: Adding Hours And Minutes To Time

Feb 11, 2011

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

View 1 Replies View Related

Covert A Time Shown As 001200 (12 Hours) To 720 Minutes

Feb 10, 2012

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?

View 1 Replies View Related

SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes

Jun 14, 2012

How to get Time Difference between two DateTime Columns in Oracle 10g ?

View 10 Replies View Related

PL/SQL :: Get Time In Hours Minutes And Seconds Subtract Between Two Varchars?

Oct 18, 2012

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

View 3 Replies View Related

PL/SQL :: Round The Number To Nearest 10k

Nov 1, 2012

I am facing small issue, i need to round the number to the nearest 10k for some finance reporting

ex
1. if number is 2250 round to 10,000
2. if number is 5500 round to 10,000
3. if number is 4912345 round to 4920000
etc..

the number should always round up to nearest 10k..

I am using round function to achieve this..

case 1:-
SQL> Select round(2250,-4) From dual;

ROUND(2250,-4)
--------------
             0

I need 10,000 here..

case 2:-
SQL> Select round(5500 ,-4) From dual;

ROUND(5500,-4)
--------------
         10000

SQL>

This is working fine...

case 3:-
SQL> Select round(4912345 ,-4) From dual;

ROUND(4912345,-4)
-----------------
          4910000

This should round UP to 4920000

let me know if there is any way i can achieve this...

View 10 Replies View Related

SQL & PL/SQL :: Round Robin Implementation Through Script?

Aug 19, 2010

I have to implement round robin algorothim in sql script for allocation of some items to another entity. Not really sure how to do that and if there would be locks while selecting the one which is already being updated and so...!

how to go about it when the number of users involved would be very high to whom the items would be allocated in round robin function.

View 15 Replies View Related

PL/SQL :: Unable To Round Date Function?

Feb 21, 2013

cant i round the date like this? or only sysdate can be rounded or if the date is stored in a variable can only be rounded

select round('21-feb-1999','mon') from dual;

View 2 Replies View Related

PL/SQL :: Function To Round To Previous Quarter

Feb 28, 2013

I need to have a function which will round to previous quarter

For ex :

if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50

View 2 Replies View Related

Performance Tuning :: Px Send Round-robin?

Jun 19, 2012

what is "px send round-robin "

View 1 Replies View Related

SQL Net Round Trips On Long Data Type Table

Oct 12, 2012

We are working on a performance tuning aspect, where in a table has a LONG datatype and number of round trips are increasing based on the number of rows fetched.i.e one round trip for every row.

Background.

1. created a table with LONG data type.
2. inserted bulk load of data.
3. set auto trace on and executed below tests..

SQL> select count(*) from test_long;

COUNT(*)
----------
6110

SQL> desc test_long
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NUMBER
TESTLONG LONG
[code].....

but on LONG column, irrespective of the array size the round trips does not reduce.

View 1 Replies View Related

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

SQL & PL/SQL :: How To Get Minutes Between Two Times

Oct 19, 2012

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.

View 5 Replies View Related

SQL & PL/SQL :: How To Subtract Minutes From Timestamp

Mar 31, 2010

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.

View 6 Replies View Related

SQL & PL/SQL :: Count Records Per Every 10 Minutes

Jun 6, 2011

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.

View 13 Replies View Related

SQL & PL/SQL :: Difference Of Dates And Get Minutes

Oct 25, 2012

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 ?

View 2 Replies View Related

Archive Log Generated Each 4 Minutes?

Jul 2, 2012

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 Related

PL/SQL :: Date Subtraction Of Minutes?

Jun 14, 2012

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

View 4 Replies View Related

Application Net Connection Is Taking 30 To 40 Minutes

Jul 20, 2012

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.

View 7 Replies View Related

Query On V$session_longops Taking Over 5 Minutes

Jan 18, 2013

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.

View 3 Replies View Related

Can Create Range Partition Of Like 10 Minutes?

Oct 18, 2012

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 Related

DB 11g On Min2K3 Take 100% Of One Core 2 To 4 Minutes After Startup

Feb 23, 2013

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

View 2 Replies View Related

PL/SQL :: How To Convert Minutes To Hour And Minute

Oct 4, 2012

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?

View 12 Replies View Related

LGWR Switch In Database Alert Log Every 3-4 Minutes

Jul 6, 2012

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 Related

SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields

Apr 9, 2013

I 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

View 8 Replies View Related

SQL & PL/SQL :: How To Get Hours / Minutes And Second From Date Datatype Field

Jan 14, 2012

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 .

View 3 Replies View Related

SQL & PL/SQL :: Adding Two Numbers To Get Hours:minutes Format?

Jul 9, 2010

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

View 6 Replies View Related

SQL & PL/SQL :: Finding Hours And Minutes Between Two Char Field?

Mar 24, 2012

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)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved