Home » Developer & Programmer » Forms » CALCULATE TIME (forms 6i, Database 11g, OS windows server.)
CALCULATE TIME [message #677411] Wed, 18 September 2019 00:16 Go to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

HI,
I want to calculate time in oracle forms. what should I have to do. According to my requirement a column "action_name" containing logon or log off values whereas the column "logon/logoff time" containing time value. I have to calculate the difference between logon and logoff time and want to get final result in text box "Total Time".
Attached pictures is more elaborated and clear.
Thanks in advance.
/foru/forum/fa/14168/0/
Re: CALCULATE TIME [message #677416 is a reply to message #677411] Wed, 18 September 2019 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you asking how you compare data from one row to another?
Or how to calculate time from that?
Or both?
Re: CALCULATE TIME [message #677423 is a reply to message #677416] Wed, 18 September 2019 12:16 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

@cookiemonster thanks for reply,

Actually I want to calculate time between logon and logoff (for how much time user remain logon). If a user logon or logoff multiple times the total calculation should appear in text box.
I hope my query is clear enough.
Re: CALCULATE TIME [message #677426 is a reply to message #677423] Thu, 19 September 2019 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
I understood that.
There's two things you need to know to make that happen.
My previous questions were asked to determine which bit you were having problems with.
You haven't answered them.
Re: CALCULATE TIME [message #677428 is a reply to message #677426] Thu, 19 September 2019 10:21 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

Have you seen my image file I attached? Please give me code when I press button the total time for which user remain logon should appear in text box total_time.
Like that,
----------- -----------------
action_name logon/logoff Time
----------- ------------------
logon 19/09/19 10:00:00
logoff 19/09/19 11:00:00
logon 19/09/19 13:00:00
logoff 19/09/19 14:00:00
-------------------------------------
------------------------------------
Total Duration: 02:00:00 (hours)
------------------------------------
Is it clear?
Re: CALCULATE TIME [message #677433 is a reply to message #677428] Fri, 20 September 2019 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes it's clear.
Are my questions not clear?
Re: CALCULATE TIME [message #677446 is a reply to message #677433] Fri, 20 September 2019 21:43 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

Thanks, so Give me solution of my problem.
Re: CALCULATE TIME [message #677447 is a reply to message #677446] Sat, 21 September 2019 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you don't understand what cookiemonster tried to say you.
I'll try to clarify it: we can't give you a solution unless and until you answer cookiemonster's questions.

Or if you prefer in algorithm way:
begin
  post questions;
  while no answer to questions loop
    wait for a post from you;
  end loop;
  give appropriate answer and solution to issue;
end;
Is this clear?

[Updated on: Sat, 21 September 2019 01:28]

Report message to a moderator

Re: CALCULATE TIME [message #677449 is a reply to message #677416] Sat, 21 September 2019 01:42 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

@cookiemonster Sorry I was not understanding your question. I want how to calculate time from that?
Please give me solution and sorry for my mistake.
Re: CALCULATE TIME [message #677453 is a reply to message #677449] Sat, 21 September 2019 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can compute a sum of differences of dates like this:
SQL> select * from t order by action_time;
ACTION_NAME ACTION_TIME
----------- -------------------
logon       20/09/2019 16:35:38
logoff      20/09/2019 16:56:07
logon       20/09/2019 17:46:38
logoff      20/09/2019 20:54:31
logon       20/09/2019 22:24:02
logoff      20/09/2019 23:27:09
logon       21/09/2019 03:57:56
logoff      21/09/2019 05:22:19
logon       21/09/2019 05:27:31
logoff      21/09/2019 06:02:14

10 rows selected.

SQL> with
  2    durations as (
  3      select action_name,
  4             action_time-lag(action_time) over (order by action_time) duration
  5      from t
  6    )
  7  select sum(duration) days,
  8         to_char(trunc(sysdate)+sum(duration),'HH24:MI:SS') hours
  9  from durations
 10  where action_name = 'logoff'
 11  /
      DAYS HOURS
---------- --------
.271238426 06:30:35

1 row selected.
The "to_char(trunc(sysdate...)" part is a trick to convert a fraction of day to hour, it works ONLY if this fraction is less than 1 otherwise you have to compute it by yourself using integer division and modulo.

[Updated on: Sat, 21 September 2019 04:11]

Report message to a moderator

Re: CALCULATE TIME [message #677458 is a reply to message #677453] Sat, 21 September 2019 06:03 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

thanks for your reply. But I changed it according to my table could not find my solution kindly make it for me according to my table.
My table name 'users_activity' below is the structure of table.
--------------------------------------------------------------------------------
username terminal sessionid osuser action_name time_in_out
--------------------------------------------------------------------------------
ASIF LO-PC 2436915 oracle LOGON 21/09/19 10:38:22
ASIF LO-PC 2436915 oracle LOGOUT 21/09/19 10:38:36

I want to calculate time against single Username.

Thanks.
Re: CALCULATE TIME [message #677459 is a reply to message #677458] Sat, 21 September 2019 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Re: CALCULATE TIME [message #677469 is a reply to message #677459] Sun, 22 September 2019 23:10 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

--My Table---
create table USERS_ACTIVITY
(
  USERNAME    VARCHAR2(30),
  TERMINAL    VARCHAR2(30),
  SESSIONID   NUMBER,
  OS_USERNAME VARCHAR2(30),
  ACTION_NAME VARCHAR2(25),
  TIME_IN_OUT VARCHAR2(50)
);
-------------------------------------------------------
entry through forms 6i.
---------------------------------------------------
declare
	username	varchar2(30);
	terminal	varchar2(30);
	sessionid	number;
	os_username	varchar2(30);
	action_name	varchar2(25);
	time_in_out	date;

begin
select a.USERNAME,a.TERMINAL, a.sessionid, a.OS_USERNAME,ACTION_NAME,to_char(timestamp+(1/24*5),'dd/mm/yy hh:mi:ss')

into username,terminal,sessionid,os_username,action_name,time_in_out from dba_audit_trail a
where a.sessionid=(select max(sessionid)from dba_audit_trail where username=user);
insert into users_activity
(
username,terminal,sessionid,os_username,action_name,time_in_out
)
values
(username,terminal,sessionid,os_username,action_name,time_in_out);
end;
commit_form;
---------------------------------------------------------------------------------------------
Following is my required result.
---------------------------------------------------------------------------------------------
action_name logon/logoff Time
----------- ------------------
logon        19/09/19 10:00:00
logoff       19/09/19 11:00:00
logon        19/09/19 13:00:00
logoff       19/09/19 14:00:00
-------------------------------------
------------------------------------
Total Duration: 02:00:00 (hours)
----------------------------------------------------------------
I hope every thing is clear enough.

--moderator edit: added [code] tags, please do it yourself in future.

[Updated on: Mon, 23 September 2019 01:03] by Moderator

Report message to a moderator

Re: CALCULATE TIME [message #677471 is a reply to message #677469] Mon, 23 September 2019 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not a question to be clear, I understood your issue and already showed you a way to do it, it is a matter to have a test case (read the link) to again show you with your table structure and some data.

Note: your code is not correct:
1/ what happens if the SQL returns several rows?
2/ your SQL will also return other audited statements

[Updated on: Mon, 23 September 2019 01:29]

Report message to a moderator

Re: CALCULATE TIME [message #677472 is a reply to message #677471] Mon, 23 September 2019 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can just modify my previous query ti get your result with your table (changes are in UPPER case):
with 
  durations as (
    select action_name, 
           TIME_IN_OUT-lag(TIME_IN_OUT) over (order by TIME_IN_OUT) duration
    from USERS_ACTIVITY
    WHERE USERNAME = <USERNAME>
  ) 
select sum(duration) days,
       to_char(trunc(sysdate)+sum(duration),'HH24:MI:SS') hours
from durations 
where action_name = 'logoff'
/
Re: CALCULATE TIME [message #677473 is a reply to message #677472] Mon, 23 September 2019 02:57 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

1. SQL not returning multiple row because against every session of user I select max(sessionid).

2. Thanks For your help. Going to upset from this discussion. I have checked your code but returns nothing. I unable to understand why it happens.
Re: CALCULATE TIME [message #677487 is a reply to message #677473] Mon, 23 September 2019 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. This not correct, sessionid is unique across active sessions, it is not across the database life.
2. I can't help, you didn't show what you did.

Re: CALCULATE TIME [message #677492 is a reply to message #677487] Mon, 23 September 2019 10:46 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

You are right but I am selecting data from builtin view "dba_audit_trail" and storing these fields
USERNAME
TERMINAL,
sessionid
OS_USERNAME
ACTION_NAME
to_char(timestamp+(1/24*5),'dd/mm/yy hh:mi:ss')
--into my table "USERS_ACTIVITY" using when button pressed trigger. So when I pressed button at that time max(sessionid) against a user returned single value. So this is not a issue.
The issue is that how I can calculate time?
If you have an ambiguity you can clear it. I need answer I shall be thankful to you.
Re: CALCULATE TIME [message #677493 is a reply to message #677492] Mon, 23 September 2019 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
So when I pressed button at that time max(sessionid) against a user returned single value. So this is not a issue.

Once again, you can have this same max(sessionid) for one user and different sessions in dba_audit_trail.

Quote:
The issue is that how I can calculate time?

With the queries I gave.

If you can't provide INSERT statements then export the table you have, post the dump and I'll show .

Re: CALCULATE TIME [message #677495 is a reply to message #677493] Mon, 23 September 2019 12:10 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

sir You are great you are may be right. So Please told me how I can manage unique entries for this table.
Moreover the dmp file is not allowed to attach. Hence data of table users_activity attached in .csv file
Re: CALCULATE TIME [message #677497 is a reply to message #677495] Mon, 23 September 2019 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course you have no rows with my queries for 2 reasons:
1/ your action name are not 'logon' and 'logoff' but 'LOGON' and 'LOGOUT'
2/ Your time are stored in VARCHAR2 column and not in DATE one.

So fixing that (choosing current user is HIJAB):
SQL> select action_name,
  2         to_date(TIME_IN_OUT,'DD/MM/YY HH24:MI:SS') TIME_IN_OUT
  3  from USERS_ACTIVITY
  4  where USERNAME = 'HIJAB'
  5  /
ACTION_NAME TIME_IN_OUT
----------- -------------------
LOGON       23/09/2019 10:14:04
LOGOUT      23/09/2019 10:59:04
LOGON       23/09/2019 02:16:26
LOGOUT      23/09/2019 03:16:26

4 rows selected.

SQL> with
  2    data as (
  3       select action_name,
  4              to_date(TIME_IN_OUT,'DD/MM/YY HH24:MI:SS') TIME_IN_OUT
  5       from USERS_ACTIVITY
  6       where USERNAME = 'HIJAB'
  7    ),
  8    durations as (
  9      select action_name,
 10             TIME_IN_OUT-lag(TIME_IN_OUT) over (order by TIME_IN_OUT) duration
 11      from data
 12    )
 13  select sum(duration) days,
 14         to_char(trunc(sysdate)+sum(duration),'HH24:MI:SS') hours
 15  from durations
 16  where action_name = 'LOGOUT'
 17  /
      DAYS HOURS
---------- --------
.072916667 01:45:00

1 row selected.

[Updated on: Sun, 29 September 2019 01:54]

Report message to a moderator

Re: CALCULATE TIME [message #677502 is a reply to message #677497] Tue, 24 September 2019 00:49 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

Problem remain same I execute the code you have send to me resultant no rows selected.
According to my analysis the reason is that. when I convert "TIME_IN_OUT" into sysdate as
select action_name,
to_date(TIME_IN_OUT,'DD/MM/YY HH24:MI:SS') TIME_IN_OUT
from USERS_ACTIVITY
where USERNAME = 'HIJAB' and time_in_out=to_date(sysdate,'DD/MM/YY');
-- It shows me error ORA-01843:Not a valid month.
------------------------------------------------------------------------------
This is the reason why we are fail to reach our goal. Please solve this sing issue. error file is attached as well.
Thank you sir.
  • Attachment: garsamba.jpg
    (Size: 79.75KB, Downloaded 128 times)

[Updated on: Tue, 24 September 2019 00:50]

Report message to a moderator

Re: CALCULATE TIME [message #677504 is a reply to message #677502] Tue, 24 September 2019 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SYSDATE is a DATE, you must not apply TO_DATE on SYSDATE.

Re: CALCULATE TIME [message #677521 is a reply to message #677504] Tue, 24 September 2019 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
To get everything for a particular day you should do this:
time_in_out >= trunc(sysdate) --greater than or equal to midnight
And time_in_out < trunc(sysdate) +1; --less than midnight tomorrow
Re: CALCULATE TIME [message #677613 is a reply to message #677521] Mon, 30 September 2019 22:49 Go to previous messageGo to next message
asifcs
Messages: 21
Registered: May 2019
Location: pakistan
Junior Member

Thanks for your help. But I make an alternative for this. and and maintaining user activity manually. Because, do_date function was not working for me. Now guide me how I can calculate time from my table. Below is description of my table and data.
SRLNO NUMBER(10)
LOGUSER VARCHAR2(20) Y
PC_INFO VARCHAR2(20) Y
OS_USER VARCHAR2(20) Y
INDATE DATE Y
OUTDATE DATE Y
--------------------------------------------------------------------------------------------------------------

SRLNO LOGUSER PC_INFO OS_USER INDATE OUTDATE
-------------------------------------------------------------------------------------------------------------
195 ASIF MAIN-PC Administrator 30/09/2019 4:29:17 PM 30/09/2019 4:29:23 PM
196 ASIF MAIN-PC Administrator 30/09/2019 4:29:32 PM 30/09/2019 4:30:33 PM
197 ASIF MAIN-PC Administrator 30/09/2019 4:34:44 PM 30/09/2019 4:34:56 PM
198 ASIF MAIN-PC Administrator 30/09/2019 4:35:40 PM 30/09/2019 4:36:04 PM
142 ASIF MAIN-PC Administrator 27/09/2019 4:18:12 PM 27/09/2019 4:19:08 PM
160 ASIF MAIN-PC Administrator 30/09/2019 10:32:34 AM 30/09/2019 10:56:42 AM

How I can calculate time diference between indate and outdate.
Re: CALCULATE TIME [message #677615 is a reply to message #677613] Tue, 01 October 2019 00:58 Go to previous message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 21 September 2019 15:01

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Quote:
Because, do_date function was not working for me

TO_DATE works for everybody who properly use it.

Quote:
How I can calculate time diference between indate and outdate.

Use minus ("-").

Previous Topic: upload/Copy file using forms 6i.
Next Topic: Sending Sms From Oracle data base Using Cellular mobile company API (merged 2)
Goto Forum:
  


Current Time: Fri Dec 06 10:01:13 CST 2019