OraFAQ Forum: SQL & PL/SQL » get number of seconds between two DATE (min and max) and calculate trans/sec

Home » SQL & PL/SQL » SQL & PL/SQL » get number of seconds between two DATE (min and max) and calculate trans/sec (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

get number of seconds between two DATE (min and max) and calculate trans/sec [message #599281]

Wed, 23 October 2013 08:20

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

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)
 STATUS                                    NOT NULL NUMBER(11)
 SERIAL_NEG                                         NUMBER(11)
 RET_CODE                                           NUMBER(11)
 PRE_MSG                                            VARCHAR2(4000 CHAR)
 ACT_MSG                                            VARCHAR2(4000 CHAR)
 POST_MSG                                           VARCHAR2(4000 CHAR)
 PLATFORM_NAME                                      VARCHAR2(64 CHAR)
 SHORT_NAME                                NOT NULL VARCHAR2(2 CHAR)
 RSS_NAME                                           VARCHAR2(32 CHAR)
 RSS_TYPE                                           VARCHAR2(12 CHAR)
 MSG_TYPE                                           VARCHAR2(1 CHAR)
 REC_TYPE                                           VARCHAR2(1 CHAR)
 COMM_MSG                                           CLOB
 ADMIN                                              VARCHAR2(255 CHAR)
 ADMIN_GROUP                                        VARCHAR2(255 CHAR)
 FIND_KEYS                                          VARCHAR2(700 CHAR)
 ESS_USER                                  NOT NULL VARCHAR2(20 CHAR)
 SIID                                               VARCHAR2(9 CHAR)
 HOT_PATH                                           VARCHAR2(1 CHAR)
 ORIGIN                                             VARCHAR2(32 CHAR)
 SYNC_PW                                            VARCHAR2(1 CHAR)
 INITIAL_LOAD                                       NUMBER(11)
 DOWNLOAD_METHOD                                    NUMBER(11)
 USER_BY_NAME                                       VARCHAR2(255 CHAR)
 USER_BY_PREFIX                                     VARCHAR2(255 CHAR)
 USER_BY_UG                                         VARCHAR2(255 CHAR)
 OE_BY_NAME                                         CLOB
 UG_BY_NAME                                         VARCHAR2(255 CHAR)
 USER_ID                                            VARCHAR2(20 CHAR)
 RSS_USER_NAME                                      VARCHAR2(511 CHAR)
 UG_NAME                                            VARCHAR2(255 CHAR)
 RES_ID                                             NUMBER(11)
 ACE_ID                                             NUMBER(11)
 OE_FULL_NAME                                       VARCHAR2(255 CHAR)
 EXTERNAL_ID                                        NUMBER(11)
 REMARKS                                            VARCHAR2(2000 CHAR)
 ONLINE_MODE                               NOT NULL NUMBER(11)
 PARENT_ID                                          NUMBER(11)
 PRIORITY                                           NUMBER(11)
 AGENT_RELEASE                                      VARCHAR2(12 CHAR)
 HD_SHOULD_CREATE_TICKET                            VARCHAR2(1 CHAR)
 HD_TICKET_ID                                       VARCHAR2(64 CHAR)
 HD_TICKET_WAS_PROCESSED                            VARCHAR2(1 CHAR)
 ORIGINAL_COMMAND                                   VARCHAR2(32 CHAR)
 TIME_CREATED                                       VARCHAR2(23 CHAR)
 TIME_SENT                                          VARCHAR2(23 CHAR)
 TIME_STARTED                                       VARCHAR2(23 CHAR)
 TIME_AGENT_RESPONDED                               VARCHAR2(23 CHAR)
 TIME_ENDED                                         VARCHAR2(23 CHAR)

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

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599283 is a reply to message #599281]

Wed, 23 October 2013 08:26

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

When you just substract MIN (upd_time) from MAX (upd_time), then you get the difference "in days".

That you can of course multiply by 86400 to get the difference "in seconds", no need to extract anything.

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599291 is a reply to message #599281]

Wed, 23 October 2013 08:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

calculate trans/sec

COUNT(serial)/(MAX(upd_time)-MIN(upd_time))/86400

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599292 is a reply to message #599283]

Wed, 23 October 2013 08:44

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

ThomasG wrote on Wed, 23 October 2013 08:26

When you just substract MIN (upd_time) from MAX (upd_time), then you get the difference "in days".

That you can of course multiply by 86400 to get the difference "in seconds", no need to extract anything.

thanks

resulting code

SQL> SELECT COUNT (SERIAL) / ( (MAX (UPD_TIME) - MIN (UPD_TIME)) * 86400)
  FROM (SELECT * FROM TRANS
        UNION ALL
        SELECT * FROM TRANS_ARCHIVE); 

COUNT(SERIAL)/((MAX(UPD_TIME)-MIN(UPD_TIME))*86400)
---------------------------------------------------
                                         .059634424

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599294 is a reply to message #599291]

Wed, 23 October 2013 08:47

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Michel Cadot wrote on Wed, 23 October 2013 08:44

Quote:
calculate trans/sec

COUNT(serial)/(MAX(upd_time)-MIN(upd_time))/86400

is it not

COUNT(serial)/(MAX(upd_time)-MIN(upd_time))*86400

[Updated on: Wed, 23 October 2013 08:48]

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599297 is a reply to message #599294]

Wed, 23 October 2013 08:52

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have a look at parentheses in both our (correct) answers in our previous posts.
Your last expression misses one parenthesis level (when your previous one had it).

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599298 is a reply to message #599294]

Wed, 23 October 2013 08:56

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Well, it's like '(10 / (3-2)) / 6' in one way, like '10 / ((3-2) * 6)' in the other way, which will both result in 1,66666666....

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599299 is a reply to message #599297]

Wed, 23 October 2013 08:56

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I'll just shutup and try to read stuff properly Sad

[Updated on: Wed, 23 October 2013 08:57]

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599302 is a reply to message #599281]

Wed, 23 October 2013 09:15

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

An observation, your table trans has 5 columns in the last as TIME_*, declared as character type. Better use INTERVAL type if you want to store only the time interval.

And, an interval is what is returned from the difference between two
TIMESTAMPS.

A number is what is returned from the difference of two DATES.

This asktom link might help you,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2002757912489

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599307 is a reply to message #599302]

Wed, 23 October 2013 09:40

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Lalit Kumar B wrote on Wed, 23 October 2013 10:15

Better use INTERVAL type if you want to store only the time interval.

Don't suggest INTERVAL. You must understand what arithmetic is allowed for intervals:

SCOTT@orcl > select interval '10' second + interval '10' second from dual;

INTERVAL'10'SECOND+INTERVAL'10'SECOND
---------------------------------------------------------------------------
+000000000 00:00:20.000000000

SCOTT@orcl > select interval '10' second / interval '10' second from dual;
select interval '10' second / interval '10' second from dual
                              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND


SCOTT@orcl > select 10 / interval '10' second from dual;
select 10 / interval '10' second from dual
          *
ERROR at line 1:
ORA-30081: invalid data type for datetime/interval arithmetic


SCOTT@orcl >

SY.

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599313 is a reply to message #599307]

Wed, 23 October 2013 10:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Solomon Yakobson wrote on Wed, 23 October 2013 20:10

Lalit Kumar B wrote on Wed, 23 October 2013 10:15
Better use INTERVAL type if you want to store only the time interval.

Don't suggest INTERVAL.

Well, I only had the thought that INTERVAL is better than character because you can add that to a timestamp. I did not think of other arithmetic operations like division. It was an alternative for the already incorrect design, so cannot be perfect.

So, at first place, a date and time should be stored together, why to split? They always go together. We can mask it the way we want to display the required format. A bit off-topic, but, if someone is too specific about the space being occupied to store DATE, CHARACTER, NUMBER or INTERVAL data types then it will be out of scope of this discussion.

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599393 is a reply to message #599302]

Thu, 24 October 2013 02:59

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Lalit Kumar B wrote on Wed, 23 October 2013 09:15

An observation, your table trans has 5 columns in the last as TIME_*, declared as character type. Better use INTERVAL type if you want to store only the time interval.

And, an interval is what is returned from the difference between two
TIMESTAMPS.

A number is what is returned from the difference of two DATES.

This asktom link might help you,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2002757912489

Hi Lalit

thanks for suggestion. With you input I learn.

see screenshot of 5 rows example data. IMHO data type VARCCHAR2 and INTERVAL is wrong. It's not INTERVAL, it's exact timestamp.
The vendor (BMC) should made them TIMESTAMP or other date data type.

Attachment: time_trans.JPG
(Size: 115.48KB, Downloaded 700 times)

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599395 is a reply to message #599393]

Thu, 24 October 2013 03:35

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I think INTERVAL is not such a bad idea after all:
Test script:

With mytable As
( Select to_timestamp('2013/02/24 03:20:44.845', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, to_timestamp('2013/02/24 03:20:45.284', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date From dual Union All
  Select to_timestamp('2013/02/24 03:20:46.045', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, to_timestamp('2013/02/24 03:20:46.312', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date From dual Union All
  Select to_timestamp('2013/02/24 03:20:47.133', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, to_timestamp('2013/02/24 03:20:47.443', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date From dual Union All
  Select to_timestamp('2013/02/24 03:20:48.324', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, to_timestamp('2013/02/24 03:20:48.638', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date From dual Union All
  Select to_timestamp('2013/02/24 03:20:48.924', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, to_timestamp('2013/02/24 03:20:49.229', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date From dual 
)
Select extract( second from (end_date - start_date) Day To Second) Diff
From   mytable
/

Test run:

SQL> @orafaq

      DIFF
----------
      .439
      .267
       .31
      .314
      .305

SQL>

Perhaps I'm missing some point here, but wasn't the question how to get the difference between two dates expressed in seconds?

MHE

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599397 is a reply to message #599395]

Thu, 24 October 2013 03:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If the dates are not within the minute the result is wrong:

SQL> With mytable As
  2  ( Select to_timestamp('2013/02/24 03:20:44.845', 'YYYY/MM/DD HH24:MI:SS.FF3') start_date, 
  3           to_timestamp('2013/02/25 04:21:46.284', 'YYYY/MM/DD HH24:MI:SS.FF3') end_date 
  4    From dual )
  5  Select end_date - start_date diff,
  6         extract( second from (end_date - start_date) Day To Second) Diff
  7  From   mytable
  8  /
DIFF                                                                              DIFF
--------------------------------------------------------------------------- ----------
+000000001 01:01:01.439000000                                                    1.439

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599400 is a reply to message #599397]

Thu, 24 October 2013 03:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Valid point, Michel. I should have elaborated it a little bit. When looking back at this thread, I see that the problem is not the use of the INTERVAL, but the data type that is the point here. My bad.

MHE

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599411 is a reply to message #599400]

Thu, 24 October 2013 04:52

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Could it be as simple as this? Warning: untested code

SELECT   EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME)) Day To Second) * 24 * 60 * 60
       + EXTRACT (HOUR FROM (MAX (UPD_TIME) - MIN (UPD_TIME)) Day To Second) * 60 * 60
       + EXTRACT (MINUTE FROM (MAX (UPD_TIME) - MIN (UPD_TIME)) Day To Second) * 60
       + EXTRACT (SECOND FROM (MAX (UPD_TIME) - MIN (UPD_TIME)) Day To Second)
          DELTA
  FROM (SELECT * FROM TRANS
        UNION ALL
        SELECT * FROM TRANS_ARCHIVE);

MHE

[Updated on: Thu, 24 October 2013 04:53]

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599414 is a reply to message #599411]

Thu, 24 October 2013 05:12

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Maaher: that works

result

88769797

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599468 is a reply to message #599411]

Thu, 24 October 2013 12:55

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Maaher wrote on Thu, 24 October 2013 05:52

Could it be as simple as this?

I don't see simple. All that code is nothing but:

(MAX(UPD_TIME) - MIN(UPD_TIME)) * 86400

if UPD_TIME is DATE and

((SYDATE + (MAX(UPD_TIME) - MIN(UPD_TIME))) - SYSDATE) * 86400

if it is TIMESTAMP.

SY.

[Updated on: Thu, 24 October 2013 12:59]

Report message to a moderator

Re: get number of seconds between two DATE (min and max) and calculate trans/sec [message #599514 is a reply to message #599468]

Fri, 25 October 2013 00:36

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Granted.

I meant that the OP was really close to a solution himself. He only needed to add three simple keywords (for each element) to his original query. The modification as such was not really complex.

MHE

Report message to a moderator

Previous Topic:	Oracle Stored Procedure to Load Flat File Data
Next Topic:	[SOLVED] Aliases, a lesson learnt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 16:33:18 CDT 2024