SQL & PL/SQL :: Use Of Sub Seconds In Timestamp

Feb 11, 2011

What is the exact usage of the SUBSECONDS in Timestamp data type.

Especially it has range upto 9 decimal places?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Record Between Seconds

Nov 9, 2011

I have a date column in a table, I need to fetch records from 27:11:11 00:00:10 to 30:11:11 00:00:10 (DD:MM:YY HH:MI:SS). i.e between seconds/min/hrs.

expecting Something like this,

SELECT * FROM TABLENAME WHERE MODEDATE BETWEEN
TO_CHAR('27/11/11 00:00:10','DD-MN-YY HH24:MI:SS') AND
TO_CHAR('30/11/11 01:10:10','DD-MN-YY HH24:MI:SS')

View 6 Replies View Related

SQL & PL/SQL :: How To Get Date In Seconds

Oct 23, 2012

I have a date field which is passed as in parameter defaulted to sydate With the passed date as input I need to run an eod for every 15 mins by calculating

v_sdate := TRUNC ( in_sdate
- (MOD (TO_CHAR (in_sdate, 'MI'), 15) * 60)
/ (24 * 60 * 60),
'MI'
);
v_edate := TRUNC (in_sdate, 'MI');

Using the above v_sdate and v_edate I am calculating the start date and end date and compare these in my final select query.Now in case of rerun of old date, I am calculating start date as : v_sdate := TRUNC (in_sdate, 'MI') - 15 / 1440;

But in the above calculation, I am not considering seconds, but I need to consider those also, because say I ran the eod at 23-oct-2012 12:23:13 then my start date in case of re run should start from 12:23:14 secs , how can I achieve that?

View 3 Replies View Related

SQL & PL/SQL :: Get Number Of Seconds Between Two Dates (min And Max)?

Oct 23, 2013

SQL> SELECT MAX (upd_time), MIN (upd_time), COUNT (serial)
FROM (SELECT * FROM trans
UNION ALL
SELECT * FROM trans_archive);
MAX(UPD_T MIN(UPD_T COUNT(SERIAL)
--------- --------- -------------
23-OCT-13 01-JAN-11 5289261

I need to calculate seconds between MAX (upd_time) and MIN (upd_time) and then calculate trans/sec. Number of trans COUNT (serial).

SQL> desc trans;
Name Null? Type
----------------------------------------- -------- ----------------------------
SERIAL NOT NULL NUMBER(11)
UPD_TIME NOT NULL DATE
MESSAGE NOT NULL VARCHAR2(255 CHAR)
ENTITY_TABLE NOT NULL VARCHAR2(32 CHAR)
ACTION NOT NULL VARCHAR2(12 CHAR)

[code]....

trans_archive the same DDL.

my first try to get intervall between max and min date in secons:

SQL> SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60
+ EXTRACT (HOUR FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60 * 60
+ EXTRACT (MINUTE FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60
+ EXTRACT (SECOND FROM (MAX (UPD_TIME) - MIN (UPD_TIME)))
DELTA
FROM (SELECT * FROM TRANS
UNION ALL
SELECT * FROM TRANS_ARCHIVE);
SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60
*
ERROR at line 1:
ORA-30076: invalid extract field for extract source

View 18 Replies View Related

SQL & PL/SQL :: Conversion Of Milliseconds / Seconds To HH:MM:SS Format

Feb 13, 2012

I ran this query :

SELECT el.date_time_stamp AS occurance_time,
esl.ack_time_stamp AS response_time,
esl.res_time_stamp AS resolved_time,
NVL ( (esl.ack_time_stamp - el.date_time_stamp), 0)
AS time_taken_to_acknowledge,
NVL ( (esl.res_time_stamp - el.date_time_stamp),0 )

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Calculating The Difference Of Timestamps In Seconds

Jan 2, 2013

I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.

Fields:

The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.

For example:

Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.

Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s

Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s

Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.

Row 11: Is empty because there are no values to compare.

Example Table

TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000--11--UP--9801
02.01.13 12:44:03,236000000--12--DOWN--97346
02.01.13 14:05:03,845000000--11--DOWN--92485

[Code]....

View 16 Replies View Related

Server Administration :: Archive Log Every 30 Seconds?

Mar 1, 2012

Our database was generating archivelog(50MB) every 30seconds! I think this is not normal because what I did is open our database, I was the only one who is connected, I'm not running anything, but our database is still generating archivelogs!

Our redo logs: 6groups 3members.

This are the things I saw on our alert logs:
- advanced to log sequence
- cannot allocate new log, sequence
- checkpoint not complete
- private strand flush not complete

What I did is change the log mode of our database to noarchivelog then open the database, then returned it to archivelog mode then that fixed the problem. But the thing is after 6hours its abnormal behavior goes back again.

View 3 Replies View Related

Forms :: Days Hours Min And Seconds Calculation

Nov 5, 2010

I am able to run this query in Sql Prompt.

DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",

[Code]...

It gives proper / desired results. I want to use it on my Form, how can it be done?

View 3 Replies View Related

PL/SQL :: Date Format - Display Fraction Of Seconds

Oct 8, 2012

select to_char(SYSDATE,'MMDDYYYYHHMISS') from dual;

Ouput:
10082012010338

It is giving me correct output, but i want to display even the Fraction of seconds, how can i?

I have tried this..but not working..

select to_char(SYSDATE,'MMDDYYYYHHMISSFF') from dual;

Output: Error.

View 4 Replies View Related

Application Express :: Date Picker With Seconds?

Apr 11, 2013

I'm using Apex 4.2.1 against Oracle 11gR2 and mod_plsql.create a date picker item that allows users to select seconds as well as hours and minutes.

I have searched this Forum and see that others have asked this question. The answers have all been "No". However, I've seen no such question since version 4.1 has been released, and so, am hoping there now is a way to do this.

I have adjusted the Date Format field to be "DD-Mon-YYYY HH24:MI:SS", both under the date picker item itself as well as under the "Global" parameters section of my application. All to no avail. The date picker shows only hours and minutes for the time portion.

View 2 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

JMS Client Receives AQ Messages Asynchronously With A Delay Of 15 Seconds

May 7, 2013

I have written a stand alone (Java SE 1.6) JMS client program to consume AQ's messages via Oracle JMS API (aqapi.jar). The queue is a multiple consumer queue, and i just created one subscriber on it. My JMS client program receives messages asynchronously by setting the MessageListener using the setMessageListener method.

Watching the work of the program, I found significant delays in receiving messages that are up to several seconds. When I turned on the diagnostic trace, I found that in the absence of messages listener (AQjmsSimpleScheduler) gradually increases the delay time up to 15 seconds:

Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a non null message, the sleep time is reset to 0
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 0 millisecond.
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a null message, the sleep time is doubled to 1000
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 1000 millisecond.
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.doSleep: try to wait for 1000 milliseconds

[code]....

Thus, in the worst case, the delay between placing the message in the queue and receiving it by the JMS client is 15 seconds.

Can I control this latency? For example, I would like to explicitly set the levels of the time delays.

View 1 Replies View Related

Replication :: Setup Schedule Push To Propagate Transaction Every 5 Seconds

Aug 29, 2008

I have a multimaster advance replication environment and we have less than 10 transaction per day...I want to propagate data as soon as possible like sync if I setup the schedule push to propagate transaction every 5 seconds as below

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'orc2',
interval => 'SYSDATE + (5/60*60*24)',
next_date => SYSDATE,
parallelism => 1,
delay_seconds => 10);
END;
/

in other hand ,book(I mean Advance Replication) has written that for simulating continuous push we should setup it as below so it will propagate transaction every 1 minute.

My second question is:I know interval >= 'SYSDATE + (1/144)' means every 10 minutes a job will start and delay_seconds => 1200 means each job remain aware for 20 minutes, but I can't understand the logic?why it can simulate 1 minute propagation?

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'orc2',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_second >= 1500,
delay_seconds => 1200);
END;

My thrid question is :Which of the above setups is a better solution for my environment?

View 4 Replies View Related

Timestamp From A Row

Oct 17, 2012

I am working on a table what has duplicate id with different data set. The problem is this table does not have any time stamp. and this table is created long ago. now I need to remove those records with duplicate id. I want to keep the oldest one. How can retrieve the time stamp of those old data.

View 7 Replies View Related

SQL & PL/SQL :: Timestamp To Date

Jul 26, 2011

From ETL to Oracle, I have stored the timestamp in varchar2 as '30-MAY-11 06.30.00.000000 PM'. Now I need to convert this varchar2(timestamp format) to date. I used:

select TRUNC('30-MAY-11 06.30.00.000000 PM','dd/mm/yyyy hh24:mi') from dualBut, it doest work. get date format?

View 7 Replies View Related

SQL & PL/SQL :: Create Trigger For Timestamp

Aug 13, 2010

Create a trigger that stamps the date_created and the date_updated column with current date on new and updated records?

View 2 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 :: Fraction Of Secs In Timestamp

Mar 12, 2011

where exactly the FRACTION of seconds being used, in the TIMESTAMP Data Types.

Example in the below result.

CURRENT_TIMESTAMP
-----------------------------------------------------------------12-MAR-11 10.35.39.691342 PM +05:30

View 2 Replies View Related

SQL & PL/SQL :: How To Pass Timestamp Parameters

Sep 13, 2013

In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.

CREATE OR REPLACE
PACKAGE body ac
IS
PROCEDURE ac_ex_wbdb_keycats(
In_Sale_Location_Id IN NUMBER,
In_Start_Datatime IN TIMESTAMP,
In_Stop_Datatime IN TIMESTAMP,
v_refcur OUT sys_refcursor)

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Extracting Date From Timestamp?

Jan 18, 2012

How to extract date from a timestamp data

for example

Input-15/06/2010 05:30:00.000000
output: 15/06/2010

View 3 Replies View Related

SQL & PL/SQL :: Ora-04062 Timestamp Changed

Apr 4, 2012

I had follwoing function

Create Or Replace Function Fin_Prd(V_Dte In Date) Return Number Is
V_Fin_Str Number;
Begin

[Code].....

The above function was running well. Today i have made some change as under

Create Or Replace Function Fin_Prd(V_Dte In Date,V_Rtn_Flg In Number Default 0) Return Number Is
V_Fin_Str Number;

[Code]....

Above function is created and working well when i use it in query in sql prompt or Toad. But problem is this that all function which used this are invalid and when i run report whose query use FIN_PRD then error is "Ora-04062. Timestamp of Fin_Prd has been changed".

View 6 Replies View Related

SQL & PL/SQL :: Distinct Timestamp - One Entry Per Day?

Apr 21, 2010

how can I distinct this query to get just one entry for one day.

this query:

select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp

returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.

but i want to have:

Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.

View 5 Replies View Related

Timestamp Of A Command Executed

Sep 17, 2012

OS:Linux 5

how to find out the timestamps of a OS command executed previously. "history" command doesn't show the timestamps. I checked "man history" but i couldn't able to find the proper option.

1222 db
1223 oh
1224 cd dbs
1224 cd $TNS_ADMIN

View 5 Replies View Related

PL/SQL :: Check If Time Is In Between Two Timestamp

Jun 17, 2013

I have a simple query which will return either A or B depending on the projected oven out date and time. If the projected oven out date and time is between 6am and 6pm, A should be returned. Otherwise B if time is between 6pm and 6am of the next day. My problem is that I do not know how to display A or B depending on the projected oven out date and time. I am using the query below to get the projected oven out date and time.  

SELECT
to_char((ti.txndate + pm.baketime/24),'MM/DD/YYYY HH:MI:SS PM') FCSTDOvenOut  
FROM CONTAINER c

[Code].....

View 3 Replies View Related

PL/SQL :: Timestamp With Time Zone

Sep 7, 2013

will Import into a table I am getting the below error message Error Message

Record 1: Rejected - Error on table MTN_BUNDLES_EXPIRY_MIG, column EXPIRY_DATE_T.

ORA-01840: input value not long enough for date format The data client provide in .XLs file  2013-08-31 17:14:56My Table Structure is  

CREATE TABLE tmp_mtnuga_3g_expiry_mig
(
    MSISDN_V            VARCHAR2 (50),
    expiry_Date_t         TIMESTAMP(6) WITH TIME ZONE
    status_date_t          TIMESTAMP(6) WITH TIME ZONE
    GOT_STARTER_PACK_V  NUMBER(10)
);

I am using 2 option to import into a table First option using Toad ---> Import Table -- option here i am getting error likeThe format is not matched.Second option using SQL Loader-->

LOAD DATA
INFILE 'D:ssTT-ProjectsCustomer AppsClient Dump3GBundle.csv'
BADFILE 'D:ddTT-ProjectsCustomer AppsClient Dump3GBundle.bad'
DISCARDFILE 'D:ddTT-ProjectsCustomer AppsClient Dump3GBundle.dsc'

[Code]...

how solve the TimeStamp

View 7 Replies View Related

Last Modified Timestamp Of A Table

Jul 8, 2013

Provide me a query to find the last modified timestamp of all the tables present in a schema? 

View 5 Replies View Related

SQL & PL/SQL :: How To Subtract Timestamp Values

Apr 18, 2012

Oracle version:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

how to subract the timestamp values.

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

This is my output.

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM

Now how to subtract the FF value in sysdate.(ex:10000 this is the input of FF value )

example output like: "18-APR-12 09.46.44.005196 AM"

View 10 Replies View Related

How To Append Timestamp To Log File In SQL*Plus

May 9, 2013

Version: 11.2.0.3
Platform : RHEL 5.8 (But I am looking for platform independant solution)

I want to append the timestamp to spooled log file name in SQL*Plus.The spooled log filename should look like

WMS_APP_23-March-2013.logI tried the following 3 methods found in the google. But none of them worked !

I tried this

col sysdt noprint new_value sysdt_var
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt_var.Logas suggested in

[URL]

and this

spool filename with timestamp
col sysdt noprint new_value sysdt
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt..Logas suggested in

[URL]

and this

column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
prompt &file_time
spool logfile_id&file_time..logas suggested in

Creating a spool file with date/time appended to file name

None of the above worked in RHEL or MS DOS.

View 6 Replies View Related

How To Get Shorter Date Format (without Timestamp)

Sep 11, 2009

I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.

This is what I get: 2009-08-04 00:00:00

This is what I want: 2009-08-04

View 3 Replies View Related

TIMESTAMP Parameter In Procedure / What Format Would It Take

Jan 30, 2009

I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g

function_name( 'timestamp')

--would this be;

function_name('12:00');

--or something along those lines?

View 1 Replies View Related







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