|
|
|
Re: CALCULATE TIME [message #677426 is a reply to message #677423] |
Thu, 19 September 2019 03:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #677447 is a reply to message #677446] |
Sat, 21 September 2019 01:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 #677453 is a reply to message #677449] |
Sat, 21 September 2019 04:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 #677469 is a reply to message #677459] |
Sun, 22 September 2019 23:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/06550b5b1ba5075f8a939a771a8fa811?s=64&d=mm&r=g) |
asifcs
Messages: 21 Registered: May 2019 Location: pakistan
|
Junior Member |
![asifghumancs@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
--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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 #677497 is a reply to message #677495] |
Mon, 23 September 2019 14:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 #677521 is a reply to message #677504] |
Tue, 24 September 2019 07:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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
|
|
|
|
|