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 Go to next message
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 #679913 is a reply to message #679912] Mon, 13 April 2020 04:00 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

And, by the way,
orclz>
orclz> with times as (select systimestamp sooner, systimestamp+10/1440 later from dual)
  2  select later - sooner from times;

LATER-SOONER
---------------------------------------------------------------------------
+000000000 00:09:59.211000

orclz>
Re: Calculating the Difference of Timestamp in Min, Sec [message #679914 is a reply to message #679912] Mon, 13 April 2020 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You get the difference directly using minus:
SQL> select (systimestamp+numtodsinterval(20.22, 'second')) - systimestamp from dual;
(SYSTIMESTAMP+NUMTODSINTERVAL(20.22,'SECOND'))-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 00:00:20.220000000
Re: Calculating the Difference of Timestamp in Min, Sec [message #679915 is a reply to message #679914] Mon, 13 April 2020 12:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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 #679924 is a reply to message #679919] Tue, 14 April 2020 02:31 Go to previous messageGo to next message
Giles02
Messages: 3
Registered: April 2020
Junior Member
Hi Michael,

What you have provided is perfect!!

Many thanks for your input & advice.


Kind Regards
Giles
Re: Calculating the Difference of Timestamp in Min, Sec [message #679934 is a reply to message #679924] Tue, 14 April 2020 06:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Giles02 wrote on Tue, 14 April 2020 03:31

What you have provided is perfect!!
Well, you asked about the difference between two Timestamp(6) columns in minutes and seconds. What was provided is minutes and seconds from the difference. So it will work only if the difference is less than an hour.

SY.


[Updated on: Tue, 14 April 2020 06:13]

Report message to a moderator

Re: Calculating the Difference of Timestamp in Min, Sec [message #679937 is a reply to message #679934] Tue, 14 April 2020 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Even less than 59' 59.5" unless 59' 60" is accepted. Smile

Re: Calculating the Difference of Timestamp in Min, Sec [message #679938 is a reply to message #679924] Tue, 14 April 2020 07:45 Go to previous message
Michel Cadot
Messages: 67363
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

Previous Topic: create materialized view log with explicit constraint (merged)
Next Topic: Fine Grain Access control disabled but still prevents to create Materialized View
Goto Forum:
  


Current Time: Fri Sep 18 16:25:43 CDT 2020