Home » SQL & PL/SQL » SQL & PL/SQL » Calculating the Difference of Timestamp in Min, Sec (Windows 2012, 11g, 12c)
Calculating the Difference of Timestamp in Min, Sec [message #679912] |
Mon, 13 April 2020 02:20 |
|
Giles02
Messages: 3 Registered: April 2020
|
Junior Member |
|
|
Hello Community,
I am trying to complete a report to display the difference between two Timestamp(6) columns in minutes and seconds. I have attempted different formatting functions but I can't seem to able to find the right combination to achieve the correct results?
Format of Timestamp Records
===========================
28-OCT-19 10.39.34.577000000 28-OCT-19 10.39.40.000000000
28-OCT-19 10.39.40.000000000 28-OCT-19 10.39.41.000000000
28-OCT-19 10.40.46.000000000 28-OCT-19 10.40.48.000000000
SELECT
to_char(min(r.check_start_tstamp),'dd/mm/yyyy hh24:mi:ss') time_in,
to_char(max(r.check_end_tstamp),'dd/mm/yyyy hh24:mi:ss') time_out,
extract(minute from (max(r.check_end_tstamp) - min(r.check_start_tstamp))) mins,
ROUND((extract(second from (max(r.check_end_tstamp) - min(r.check_start_tstamp))/60)), 2) Secs
FROM qg_check_result r
where check_list_number='1221830'
OUTPUT:-
========
TIME_IN TIME_OUT Mins SECS
28/10/2019 10:39:34 28/10/2019 10:40:48 1 1.22
Is there a way to do this?
Kind Regards
Giles
|
|
|
|
|
Re: Calculating the Difference of Timestamp in Min, Sec [message #679915 is a reply to message #679914] |
Mon, 13 April 2020 12:30 |
|
Giles02
Messages: 3 Registered: April 2020
|
Junior Member |
|
|
Hi,
Thanks for the welcome and the advice. I am providing further details of sample data that I am working with & details of what I am trying to achieve.
The Create table & Inserts are as follows:-
CREATE TABLE qg_check_result
(Time_ID NUMBER(15),
check_start_tstamp Timestamp(6) not null,
check_end_tstamp Timestamp(6) not null,
check_list_number NUMBER);
INSERT INTO qg_check_result VALUES(1, '28-OCT-19 10.39.34.577000000','28-OCT-19 10.39.40.000000000','1221830');
INSERT INTO qg_check_result VALUES(2, '28-OCT-19 10.39.40.000000000','28-OCT-19 10.39.41.000000000','1221830');
INSERT INTO qg_check_result VALUES(3, '28-OCT-19 10.40.46.000000000','28-OCT-19 10.40.48.000000000','1221830');
COMMIT;
Select Statement:-
SELECT
to_char(min(r.check_start_tstamp),'dd/mm/yyyy hh24:mi:ss') time_in,
to_char(max(r.check_end_tstamp),'dd/mm/yyyy hh24:mi:ss') time_out,
extract(minute from (max(r.check_end_tstamp) - min(r.check_start_tstamp))) mins,
ROUND((extract(second from (max(r.check_end_tstamp) - min(r.check_start_tstamp))/60)), 2) Secs
FROM qg_check_result r
where r.check_list_number='1221830'
What I attempting to do is create an output displaying three columns i.e Time_In, Time_Out, Lapsed_Time.
The Lapsed_Time is a calculated column = Time_Out(10:40:48) - Time_In(10:39:34). The format of the Lapsed_Column will be mi:ss e.g currently 1.22. instead of 1.14
Kind Regards
Giles
--moderator edit: code tags added, please do this yourself in future
[Updated on: Mon, 13 April 2020 12:33] by Moderator Report message to a moderator
|
|
|
Re: Calculating the Difference of Timestamp in Min, Sec [message #679918 is a reply to message #679915] |
Mon, 13 April 2020 13:42 |
|
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Your INSERT statements are wrong:
SQL> INSERT INTO qg_check_result VALUES(1, '28-OCT-19 10.39.34.577000000','28-OCT-19 10.39.40.000000000','1221830');
INSERT INTO qg_check_result VALUES(1, '28-OCT-19 10.39.34.577000000','28-OCT-19 10.39.40.000000000','1221830')
*
ERROR at line 1:
ORA-01843: not a valid month
If you name the months you have to specify the language you use.
Even if I change my language to AMERICAN your statements are wrong:
SQL> INSERT INTO qg_check_result VALUES(1, '28-OCT-19 10.39.34.577000000','28-OCT-19 10.39.40.000000000','1221830');
INSERT INTO qg_check_result VALUES(1, '28-OCT-19 10.39.34.577000000','28-OCT-19 10.39.40.000000000','1221830')
*
ERROR at line 1:
ORA-01880: the fractional seconds must be between 0 and 999999999
You there rely on the default timestamp format.
When you use INSERT statements you must use TO_TIMESTAMP specifying the format:
SQL> INSERT INTO qg_check_result VALUES(1,
2 to_timestamp('28-OCT-19 10.39.34.577000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28-OCT-19 10.39.40.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
1 row created.
SQL> INSERT INTO qg_check_result VALUES(2,
2 to_timestamp('28-OCT-19 10.39.40.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28-OCT-19 10.39.41.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
1 row created.
SQL> INSERT INTO qg_check_result VALUES(3,
2 to_timestamp('28-OCT-19 10.40.46.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28-OCT-19 10.40.48.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
1 row created.
SQL> COMMIT;
Commit complete.
Then, as I said, if you want the difference between the maximum CHECK_END_TSTAMP and the minimum CHECK_START_TSTAMP, just use minus:
SQL> SELECT
2 to_char(min(r.check_start_tstamp),'dd/mm/yyyy hh24:mi:ss') time_in,
3 to_char(max(r.check_end_tstamp),'dd/mm/yyyy hh24:mi:ss') time_out,
4 max(r.check_end_tstamp)-min(r.check_start_tstamp) diff
5 FROM qg_check_result ra
6 where r.check_list_number='1221830'
7 /
TIME_IN TIME_OUT DIFF
------------------- ------------------- ------------------------------------
28/10/0019 10:39:34 28/10/0019 10:40:48 +000000000 00:01:13.423000
1 row selected.
[Updated on: Mon, 13 April 2020 15:09] Report message to a moderator
|
|
|
Re: Calculating the Difference of Timestamp in Min, Sec [message #679919 is a reply to message #679918] |
Mon, 13 April 2020 13:56 |
|
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or if you want "mins" and "secs":
SQL> with data as (
2 SELECT
3 to_char(min(r.check_start_tstamp),'dd/mm/yyyy hh24:mi:ss') time_in,
4 to_char(max(r.check_end_tstamp),'dd/mm/yyyy hh24:mi:ss') time_out,
5 to_char(max(r.check_end_tstamp)-min(r.check_start_tstamp)) diff
6 FROM qg_check_result r
7 where r.check_list_number='1221830'
8 )
9 select time_in, time_out, diff,
10 to_number(regexp_substr(diff, ':(\d+):[^:]+$', 1, 1, null, 1)) mins,
11 ceil(to_number(regexp_substr(diff, '\d+\.\d*$'))) secs
12 from data
13 /
TIME_IN TIME_OUT DIFF MINS SECS
------------------- ------------------- --------------------------- ---------- ----------
28/10/0019 10:39:34 28/10/0019 10:40:48 +000000000 00:01:13.423000 1 14
1 row selected.
[Updated on: Mon, 13 April 2020 15:12] Report message to a moderator
|
|
|
|
|
|
Re: Calculating the Difference of Timestamp in Min, Sec [message #679938 is a reply to message #679924] |
Tue, 14 April 2020 07:45 |
|
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Giles02 wrote on Tue, 14 April 2020 09:31
Many thanks for your input & advice.
As you may see, Oracle has a "fuzzy" notion of format model. You use '.' to separate hour, minute and second when my format specifies ':'.
Actually, you can use any special character but for the character between second (SS) and sub-second (FF) parts which must be the same one in the data and format and might not be '.':
SQL> INSERT INTO qg_check_result VALUES(1,
2 to_timestamp('28/OCT$19 10.39|34/577000000','DD-MON-YYYY HH24:MI:SS/FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28\OCT:19 10%39!40?000000000','DD-MON-YYYY HH24:MI:SS?FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
1 row created.
You can force Oracle to follow a strict format using double quotes:
SQL> INSERT INTO qg_check_result VALUES(1,
2 to_timestamp('28/OCT$19 10.39|34.577000000','DD"-"MON"-"YYYY HH24":"MI":"SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28\OCT:19 10%39!40.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
to_timestamp('28/OCT$19 10.39|34.577000000','DD"-"MON"-"YYYY HH24":"MI":"SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
*
ERROR at line 2:
ORA-01861: literal does not match format string
SQL> INSERT INTO qg_check_result VALUES(1,
2 to_timestamp('28-OCT-19 10:39:34.577000000','DD"-"MON"-"YYYY HH24":"MI":"SS/FF9','NLS_DATE_LANGUAGE=AMERICAN'),
3 to_timestamp('28\OCT:19 10%39!40.000000000','DD-MON-YYYY HH24:MI:SS.FF9','NLS_DATE_LANGUAGE=AMERICAN'),
4 '1221830');
1 row created.
[Updated on: Tue, 14 April 2020 07:54] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Apr 29 15:27:54 CDT 2024
|