Convert time from a format to an int in Oracle2019 Community Moderator ElectionGet list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Convert Oracle Date into Crystal ReportsConverting oracle datetime to javascript dateConverting TimeStamp to HH:MM:SS formatOracle: Convert Date Time to Specific formatConverting data stored as text to numbers (Oracle SQL)Converting Integer into timeAlternate formatting for Oracle GROUPING-type subtotals?Oracle convert hhmmss to hh:mm:ss

How to deal with taxi scam when on vacation?

Did Ender ever learn that he killed Stilson and/or Bonzo?

How to make healing in an exploration game interesting

How do anti-virus programs start at Windows boot?

How difficult is it to simply disable/disengage the MCAS on Boeing 737 Max 8 & 9 Aircraft?

Why would a flight no longer considered airworthy be redirected like this?

Do I need life insurance if I can cover my own funeral costs?

Are all passive ability checks floors for active ability checks?

What is the significance behind "40 days" that often appears in the Bible?

Why Choose Less Effective Armour Types?

A link redirect to http instead of https: how critical is it?

Time travel from stationary position?

Most cost effective thermostat setting: consistent temperature vs. lowest temperature possible

How Could an Airship Be Repaired Mid-Flight

Does someone need to be connected to my network to sniff HTTP requests?

How could a scammer know the apps on my phone / iTunes account?

Why do Australian milk farmers need to protest supermarkets' milk price?

The difference between「N分で」and「後N分で」

How to read the value of this capacitor?

Why one should not leave fingerprints on bulbs and plugs?

Interplanetary conflict, some disease destroys the ability to understand or appreciate music

Opacity of an object in 2.8

My Graph Theory Students

How to explain that I do not want to visit a country due to personal safety concern?

Convert time from a format to an int in Oracle

2019 Community Moderator ElectionGet list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Convert Oracle Date into Crystal ReportsConverting oracle datetime to javascript dateConverting TimeStamp to HH:MM:SS formatOracle: Convert Date Time to Specific formatConverting data stored as text to numbers (Oracle SQL)Converting Integer into timeAlternate formatting for Oracle GROUPING-type subtotals?Oracle convert hhmmss to hh:mm:ss

I can't seem to figure this out. I have some rows with time in the format 00:00:00 (hh:mm:ss) and i need to calculate the total time it takes for a task.
I am unable to sum this data. Can someone advise on a way to convert this to a format i can sum or a method to calculate the total time for the task.
Thanks for any assistance. This is in an Oracle DB.

asked Mar 7 at 14:10

Outside_the_box

3

What's the data type of the time column? Is it a varchar or date column?

– Codo
Mar 7 at 14:12

DATATYPE is VARCHAR2

– Outside_the_box
Mar 7 at 16:26

add a comment |

asked Mar 7 at 14:10

Outside_the_box

3

What's the data type of the time column? Is it a varchar or date column?

– Codo
Mar 7 at 14:12

DATATYPE is VARCHAR2

– Outside_the_box
Mar 7 at 16:26

add a comment |

asked Mar 7 at 14:10

Outside_the_box

oracle

asked Mar 7 at 14:10

Outside_the_box

asked Mar 7 at 14:10

Outside_the_box

asked Mar 7 at 14:10

Outside_the_box

asked Mar 7 at 14:10

Outside_the_box

asked Mar 7 at 14:10

Outside_the_box

3

What's the data type of the time column? Is it a varchar or date column?

– Codo
Mar 7 at 14:12

DATATYPE is VARCHAR2

– Outside_the_box
Mar 7 at 16:26

add a comment |

3

What's the data type of the time column? Is it a varchar or date column?

– Codo
Mar 7 at 14:12

DATATYPE is VARCHAR2

– Outside_the_box
Mar 7 at 16:26

What's the data type of the time column? Is it a varchar or date column?

– Codo
Mar 7 at 14:12

DATATYPE is VARCHAR2

– Outside_the_box
Mar 7 at 16:26

add a comment |

1 Answer
1

active

oldest

votes

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '01:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
 'DAY'
 ) AS total_time_taken
FROM test_data;

Output:


| TOTAL_TIME_TAKEN |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '1:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL UNION ALL
 SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM(
 DATE '1970-01-01'
 + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
 + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
 + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
 - DATE '1970-01-01'
 ),
 'DAY'
 ) AS total_time
FROM (
 SELECT value,
 INSTR( value, ':', 1, 1 ) AS HM,
 INSTR( value, ':', 1, 2 ) AS MS
 FROM test_data
);

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT INTERVAL '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
 'DAY'
 ) AS total_time
FROM test_data;

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55045796%2fconvert-time-from-a-format-to-an-int-in-oracle%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '01:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
 'DAY'
 ) AS total_time_taken
FROM test_data;

Output:


| TOTAL_TIME_TAKEN |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '1:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL UNION ALL
 SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM(
 DATE '1970-01-01'
 + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
 + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
 + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
 - DATE '1970-01-01'
 ),
 'DAY'
 ) AS total_time
FROM (
 SELECT value,
 INSTR( value, ':', 1, 1 ) AS HM,
 INSTR( value, ':', 1, 2 ) AS MS
 FROM test_data
);

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT INTERVAL '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
 'DAY'
 ) AS total_time
FROM test_data;

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

add a comment |

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '01:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
 'DAY'
 ) AS total_time_taken
FROM test_data;

Output:


| TOTAL_TIME_TAKEN |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '1:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL UNION ALL
 SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM(
 DATE '1970-01-01'
 + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
 + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
 + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
 - DATE '1970-01-01'
 ),
 'DAY'
 ) AS total_time
FROM (
 SELECT value,
 INSTR( value, ':', 1, 1 ) AS HM,
 INSTR( value, ':', 1, 2 ) AS MS
 FROM test_data
);

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT INTERVAL '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
 'DAY'
 ) AS total_time
FROM test_data;

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

add a comment |

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '01:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
 'DAY'
 ) AS total_time_taken
FROM test_data;

Output:


| TOTAL_TIME_TAKEN |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '1:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL UNION ALL
 SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM(
 DATE '1970-01-01'
 + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
 + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
 + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
 - DATE '1970-01-01'
 ),
 'DAY'
 ) AS total_time
FROM (
 SELECT value,
 INSTR( value, ':', 1, 1 ) AS HM,
 INSTR( value, ':', 1, 2 ) AS MS
 FROM test_data
);

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT INTERVAL '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
 'DAY'
 ) AS total_time
FROM test_data;

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '01:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
 'DAY'
 ) AS total_time_taken
FROM test_data;

Output:


| TOTAL_TIME_TAKEN |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT '1:23:45' FROM DUAL UNION ALL
 SELECT '12:34:56' FROM DUAL UNION ALL
 SELECT '23:45:00' FROM DUAL UNION ALL
 SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM(
 DATE '1970-01-01'
 + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
 + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
 + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
 - DATE '1970-01-01'
 ),
 'DAY'
 ) AS total_time
FROM (
 SELECT value,
 INSTR( value, ':', 1, 1 ) AS HM,
 INSTR( value, ':', 1, 2 ) AS MS
 FROM test_data
);

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
 SELECT INTERVAL '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
 SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
 SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
 'DAY'
 ) AS total_time
FROM test_data;

Output:


| TOTAL_TIME |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

edited Mar 8 at 9:21

answered Mar 7 at 14:33

MT0

53.6k52756

answered Mar 7 at 14:33

MT0

53.6k52756

answered Mar 7 at 14:33

MT0

53.6k52756

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

add a comment |

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

The values aren't a clock time but time taken. So if one task took 48 hours 12 minutes and 23 seconds. Ideally i would like a way to get the hours and minutes in a format i could sum them with.

– Outside_the_box
Mar 7 at 16:28

@Outside_the_box Updated for durations longer than 24 hours.

– MT0
Mar 8 at 9:21

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ggtcf

1 Answer
1

Your Answer

Post as a guest

1 Answer
1

1 Answer
1

Post as a guest

Popular posts from this blog

Thal And Out Agency railway station See also References External links Navigation menuOfficial Web Site of Pakistan RailwaysArchivedOfficial Web Site of Pakistan Railwayseeexpanding ite

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

1 Answer 1

1 Answer 1

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Popular posts from this blog

Thal And Out Agency railway station See also References External links Navigation menuOfficial Web Site of Pakistan RailwaysArchivedOfficial Web Site of Pakistan Railwayseeexpanding ite

1 Answer
1

1 Answer
1

1 Answer
1