Oracle SQL round up time interval to next day
How to round up time interval to next day in Oracle SQL?
select apppackage
, numtodsinterval(
sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
), 'SECOND') as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
The output of this is
'com.Freesoul.Rotter' '+2969 04:32:47.000000' '3'
and desired output is
'com.Freesoul.Rotter' '2970' '3'
but if the output of query is
'com.Freesoul.Rotter' '+2969 00:00:00.000000' '3'
then desired output is
'com.Freesoul.Rotter' '2969' '3'
column period is of INTERVAL DAY(9) TO SECOND(6) type
and i won't mind if the retention_period is changed to number datatype.
I'll be grateful if anyone can suggest change in my query to attain the desired output.
sql oracle oracle11g rounding
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
add a comment |
How to round up time interval to next day in Oracle SQL?
select apppackage
, numtodsinterval(
sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
), 'SECOND') as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
The output of this is
'com.Freesoul.Rotter' '+2969 04:32:47.000000' '3'
and desired output is
'com.Freesoul.Rotter' '2970' '3'
but if the output of query is
'com.Freesoul.Rotter' '+2969 00:00:00.000000' '3'
then desired output is
'com.Freesoul.Rotter' '2969' '3'
column period is of INTERVAL DAY(9) TO SECOND(6) type
and i won't mind if the retention_period is changed to number datatype.
I'll be grateful if anyone can suggest change in my query to attain the desired output.
sql oracle oracle11g rounding
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
2
Is precision meaningful in your real-life problem? I ask becauseTRUNCis applied in the wrong place. You should compute the sum, and only then applyTRUNCto seconds (and, in fact, you should round up... or not even applyTRUNCto seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.
– mathguy
Nov 21 '18 at 18:16
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29
add a comment |
How to round up time interval to next day in Oracle SQL?
select apppackage
, numtodsinterval(
sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
), 'SECOND') as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
The output of this is
'com.Freesoul.Rotter' '+2969 04:32:47.000000' '3'
and desired output is
'com.Freesoul.Rotter' '2970' '3'
but if the output of query is
'com.Freesoul.Rotter' '+2969 00:00:00.000000' '3'
then desired output is
'com.Freesoul.Rotter' '2969' '3'
column period is of INTERVAL DAY(9) TO SECOND(6) type
and i won't mind if the retention_period is changed to number datatype.
I'll be grateful if anyone can suggest change in my query to attain the desired output.
sql oracle oracle11g rounding
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
How to round up time interval to next day in Oracle SQL?
select apppackage
, numtodsinterval(
sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
), 'SECOND') as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
The output of this is
'com.Freesoul.Rotter' '+2969 04:32:47.000000' '3'
and desired output is
'com.Freesoul.Rotter' '2970' '3'
but if the output of query is
'com.Freesoul.Rotter' '+2969 00:00:00.000000' '3'
then desired output is
'com.Freesoul.Rotter' '2969' '3'
column period is of INTERVAL DAY(9) TO SECOND(6) type
and i won't mind if the retention_period is changed to number datatype.
I'll be grateful if anyone can suggest change in my query to attain the desired output.
sql oracle oracle11g rounding
sql oracle oracle11g rounding
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
edited Nov 21 '18 at 17:38
AGRAWSAU
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
asked Nov 21 '18 at 15:49
AGRAWSAUAGRAWSAU
174
174
2
Is precision meaningful in your real-life problem? I ask becauseTRUNCis applied in the wrong place. You should compute the sum, and only then applyTRUNCto seconds (and, in fact, you should round up... or not even applyTRUNCto seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.
– mathguy
Nov 21 '18 at 18:16
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29
add a comment |
2
Is precision meaningful in your real-life problem? I ask becauseTRUNCis applied in the wrong place. You should compute the sum, and only then applyTRUNCto seconds (and, in fact, you should round up... or not even applyTRUNCto seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.
– mathguy
Nov 21 '18 at 18:16
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29
2
2
Is precision meaningful in your real-life problem? I ask because
TRUNC is applied in the wrong place. You should compute the sum, and only then apply TRUNC to seconds (and, in fact, you should round up... or not even apply TRUNC to seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.– mathguy
Nov 21 '18 at 18:16
Is precision meaningful in your real-life problem? I ask because
TRUNC is applied in the wrong place. You should compute the sum, and only then apply TRUNC to seconds (and, in fact, you should round up... or not even apply TRUNC to seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.– mathguy
Nov 21 '18 at 18:16
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29
add a comment |
2 Answers
2
active
oldest
votes
The result of your sum is in seconds, so you don't really need to convert it to an interval at all. Just divide by 60*60*24 to get the answer in days, and round it up with ceil():
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
Demo with artificial data in a CTE just to mimic your expected results, for both scenarios:
-- CTE for sample data
with retentions (apppackage, periods) as (
select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;
APPPACKAGE RETENTION_PERIOD USERS
------------------- ---------------- ----------
com.Freesoul.XYZ 2969 3
com.Freesoul.Rotter 2970 3
Your expected output shows a plain number. If you actually want it as an interval, but as the whole number of days, just pass ceil'd number into numtodsinterval or more simply (and usually faster for some reason) multiply by interval '1' day.
With the same dummy data:
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) * interval '1' day as retention_period
, count(apppackage) as users
...
APPPACKAGE RETENTION_PERIOD USERS
------------------- --------------------- ----------
com.Freesoul.XYZ +2969 00:00:00.000000 3
com.Freesoul.Rotter +2970 00:00:00.000000 3
As @mathguy pointed out, you probably don't need or want the trunc() call in there; that is removing the fractional seconds from each period before they are summed, which sounds insignificant but could easily affect the result you get.
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
add a comment |
How about this?
CASE WHEN numtodsinterval(extract (day from periods), 'DAY') = periods THEN
extract (day from periods)
ELSE
extract (day from periods) + 1
END
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415755%2foracle-sql-round-up-time-interval-to-next-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The result of your sum is in seconds, so you don't really need to convert it to an interval at all. Just divide by 60*60*24 to get the answer in days, and round it up with ceil():
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
Demo with artificial data in a CTE just to mimic your expected results, for both scenarios:
-- CTE for sample data
with retentions (apppackage, periods) as (
select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;
APPPACKAGE RETENTION_PERIOD USERS
------------------- ---------------- ----------
com.Freesoul.XYZ 2969 3
com.Freesoul.Rotter 2970 3
Your expected output shows a plain number. If you actually want it as an interval, but as the whole number of days, just pass ceil'd number into numtodsinterval or more simply (and usually faster for some reason) multiply by interval '1' day.
With the same dummy data:
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) * interval '1' day as retention_period
, count(apppackage) as users
...
APPPACKAGE RETENTION_PERIOD USERS
------------------- --------------------- ----------
com.Freesoul.XYZ +2969 00:00:00.000000 3
com.Freesoul.Rotter +2970 00:00:00.000000 3
As @mathguy pointed out, you probably don't need or want the trunc() call in there; that is removing the fractional seconds from each period before they are summed, which sounds insignificant but could easily affect the result you get.
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
add a comment |
The result of your sum is in seconds, so you don't really need to convert it to an interval at all. Just divide by 60*60*24 to get the answer in days, and round it up with ceil():
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
Demo with artificial data in a CTE just to mimic your expected results, for both scenarios:
-- CTE for sample data
with retentions (apppackage, periods) as (
select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;
APPPACKAGE RETENTION_PERIOD USERS
------------------- ---------------- ----------
com.Freesoul.XYZ 2969 3
com.Freesoul.Rotter 2970 3
Your expected output shows a plain number. If you actually want it as an interval, but as the whole number of days, just pass ceil'd number into numtodsinterval or more simply (and usually faster for some reason) multiply by interval '1' day.
With the same dummy data:
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) * interval '1' day as retention_period
, count(apppackage) as users
...
APPPACKAGE RETENTION_PERIOD USERS
------------------- --------------------- ----------
com.Freesoul.XYZ +2969 00:00:00.000000 3
com.Freesoul.Rotter +2970 00:00:00.000000 3
As @mathguy pointed out, you probably don't need or want the trunc() call in there; that is removing the fractional seconds from each period before they are summed, which sounds insignificant but could easily affect the result you get.
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
add a comment |
The result of your sum is in seconds, so you don't really need to convert it to an interval at all. Just divide by 60*60*24 to get the answer in days, and round it up with ceil():
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
Demo with artificial data in a CTE just to mimic your expected results, for both scenarios:
-- CTE for sample data
with retentions (apppackage, periods) as (
select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;
APPPACKAGE RETENTION_PERIOD USERS
------------------- ---------------- ----------
com.Freesoul.XYZ 2969 3
com.Freesoul.Rotter 2970 3
Your expected output shows a plain number. If you actually want it as an interval, but as the whole number of days, just pass ceil'd number into numtodsinterval or more simply (and usually faster for some reason) multiply by interval '1' day.
With the same dummy data:
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) * interval '1' day as retention_period
, count(apppackage) as users
...
APPPACKAGE RETENTION_PERIOD USERS
------------------- --------------------- ----------
com.Freesoul.XYZ +2969 00:00:00.000000 3
com.Freesoul.Rotter +2970 00:00:00.000000 3
As @mathguy pointed out, you probably don't need or want the trunc() call in there; that is removing the fractional seconds from each period before they are summed, which sounds insignificant but could easily affect the result you get.
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
The result of your sum is in seconds, so you don't really need to convert it to an interval at all. Just divide by 60*60*24 to get the answer in days, and round it up with ceil():
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
group by apppackage;
Demo with artificial data in a CTE just to mimic your expected results, for both scenarios:
-- CTE for sample data
with retentions (apppackage, periods) as (
select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.Rotter', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
union all
select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) as retention_period
, count(apppackage) as users
from retentions
where apppackage = 'com.Freesoul.Rotter'
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;
APPPACKAGE RETENTION_PERIOD USERS
------------------- ---------------- ----------
com.Freesoul.XYZ 2969 3
com.Freesoul.Rotter 2970 3
Your expected output shows a plain number. If you actually want it as an interval, but as the whole number of days, just pass ceil'd number into numtodsinterval or more simply (and usually faster for some reason) multiply by interval '1' day.
With the same dummy data:
select apppackage
, ceil(sum( trunc(extract (day from (periods)) * 86400
+ extract (hour from (periods)) *3600
+ extract (minute from (periods))*60
+ extract (second from (periods)))
) / 86400) * interval '1' day as retention_period
, count(apppackage) as users
...
APPPACKAGE RETENTION_PERIOD USERS
------------------- --------------------- ----------
com.Freesoul.XYZ +2969 00:00:00.000000 3
com.Freesoul.Rotter +2970 00:00:00.000000 3
As @mathguy pointed out, you probably don't need or want the trunc() call in there; that is removing the fractional seconds from each period before they are summed, which sounds insignificant but could easily affect the result you get.
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
edited Nov 21 '18 at 19:11
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
answered Nov 21 '18 at 17:43
Alex PooleAlex Poole
130k6101176
130k6101176
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
add a comment |
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
The solution worked for me..Thanks..
– AGRAWSAU
Nov 21 '18 at 17:54
add a comment |
How about this?
CASE WHEN numtodsinterval(extract (day from periods), 'DAY') = periods THEN
extract (day from periods)
ELSE
extract (day from periods) + 1
END
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
add a comment |
How about this?
CASE WHEN numtodsinterval(extract (day from periods), 'DAY') = periods THEN
extract (day from periods)
ELSE
extract (day from periods) + 1
END
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
add a comment |
How about this?
CASE WHEN numtodsinterval(extract (day from periods), 'DAY') = periods THEN
extract (day from periods)
ELSE
extract (day from periods) + 1
END
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
How about this?
CASE WHEN numtodsinterval(extract (day from periods), 'DAY') = periods THEN
extract (day from periods)
ELSE
extract (day from periods) + 1
END
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
answered Nov 21 '18 at 16:09
Wernfried DomscheitWernfried Domscheit
23.9k42857
23.9k42857
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415755%2foracle-sql-round-up-time-interval-to-next-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Is precision meaningful in your real-life problem? I ask because
TRUNCis applied in the wrong place. You should compute the sum, and only then applyTRUNCto seconds (and, in fact, you should round up... or not even applyTRUNCto seconds at all, if you will use Alex Poole's answer). To understand the issue, you are doing something like TRUNC(1.9) + TRUNC(1.9) to get 2, when in fact you should do 1.9 + 1.9 = 3.8 and then round either down or up as needed.– mathguy
Nov 21 '18 at 18:16
Thanks for pointing it out @mathguy... I probably don't need TRUNC in my actual query...
– AGRAWSAU
Nov 21 '18 at 19:29