SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE TEMP_DT ( COMPANYID VARCHAR2(1 BYTE), START_DT DATE, END_DT DATE ) Insert into TEMP_DT (COMPANYID, START_DT, END_DT) Values ('W', TO_DATE('2022/07/04 9:02:31 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2022/07/04 9:16:47 AM', 'YYYY/MM/DD HH:MI:SS AM')); Insert into TEMP_DT (COMPANYID, START_DT, END_DT) Values ('H', TO_DATE('2022/07/04 8:02:28 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2022/07/04 8:21:55 AM', 'YYYY/MM/DD HH:MI:SS AM')); Insert into TEMP_DT (COMPANYID, START_DT, END_DT) Values ('G', TO_DATE('2022/07/04 7:02:08 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2022/07/04 7:21:14 AM', 'YYYY/MM/DD HH:MI:SS AM')); Insert into TEMP_DT (COMPANYID, START_DT, END_DT) Values ('S', TO_DATE('2022/07/04 6:46:57 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2022/07/04 6:47:49 AM', 'YYYY/MM/DD HH:MI:SS AM')); COMMIT; SELECT companyid, cast(end_dt as timestamp) end_ts ,cast(start_dt as timestamp) start_ts, numtodsinterval(end_dt - start_dt, 'day') elapsed_time, (end_dt - start_dt) DAY(0) TO SECOND(0), SUBSTR((end_dt - start_dt) DAY(0) TO SECOND(0),4,2) || ' h ' || SUBSTR((end_dt - start_dt) DAY(0) TO SECOND(0),6,2) || ' m ' || SUBSTR((end_dt - start_dt) DAY(0) TO SECOND(0),8,2) ||' s ' as easy_readable FROM TEMP_DT
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear