How to calculate short hour in and short hour out in postgresql?
0
id checkin checkout
------------------------
1 12:27 18:10
2 11:00 19:59
I want this result and out checkin time fix 11:00:00
and checkout time fix 20:00:00.
i am using this query
select min(checktime)::time as checkin,
max(checktime)::time as checkout,
(case
when cast(min (a.checktime) as time) > '11:00:00' then
(extract (epoch from min (a.checktime)::time - '11:00:00')/3600 )
else 0
end) as shorthour_in ,
(case
when cast(max (a.checktime) as time) <= '20:00:00'
then (extract (epoch from '20:00:00' - max (a.checktime)::time )/3600 )
else 0
end) as shorthour_out
from attendance_FHLHR
id checkin checkout shorthour_in shorthour_out
---------------------------------------------------------
1 12:27 18:10 1.45 1.83
2 11:00 19:59 0 0.016
can any one guide me is it correct query to get / calculate hours?
sql postgresql
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
|
show 3 more comments
0
id checkin checkout
------------------------
1 12:27 18:10
2 11:00 19:59
I want this result and out checkin time fix 11:00:00
and checkout time fix 20:00:00.
i am using this query
select min(checktime)::time as checkin,
max(checktime)::time as checkout,
(case
when cast(min (a.checktime) as time) > '11:00:00' then
(extract (epoch from min (a.checktime)::time - '11:00:00')/3600 )
else 0
end) as shorthour_in ,
(case
when cast(max (a.checktime) as time) <= '20:00:00'
then (extract (epoch from '20:00:00' - max (a.checktime)::time )/3600 )
else 0
end) as shorthour_out
from attendance_FHLHR
id checkin checkout shorthour_in shorthour_out
---------------------------------------------------------
1 12:27 18:10 1.45 1.83
2 11:00 19:59 0 0.016
can any one guide me is it correct query to get / calculate hours?
sql postgresql
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
1
What data type are the columnscheckinandcheckout? Thecast()seems to implicate you are storing atimevalue in avarcharcolumn.
– a_horse_with_no_name
Nov 15 '18 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09
|
show 3 more comments
0
0
0
id checkin checkout
------------------------
1 12:27 18:10
2 11:00 19:59
I want this result and out checkin time fix 11:00:00
and checkout time fix 20:00:00.
i am using this query
select min(checktime)::time as checkin,
max(checktime)::time as checkout,
(case
when cast(min (a.checktime) as time) > '11:00:00' then
(extract (epoch from min (a.checktime)::time - '11:00:00')/3600 )
else 0
end) as shorthour_in ,
(case
when cast(max (a.checktime) as time) <= '20:00:00'
then (extract (epoch from '20:00:00' - max (a.checktime)::time )/3600 )
else 0
end) as shorthour_out
from attendance_FHLHR
id checkin checkout shorthour_in shorthour_out
---------------------------------------------------------
1 12:27 18:10 1.45 1.83
2 11:00 19:59 0 0.016
can any one guide me is it correct query to get / calculate hours?
sql postgresql
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
id checkin checkout
------------------------
1 12:27 18:10
2 11:00 19:59
I want this result and out checkin time fix 11:00:00
and checkout time fix 20:00:00.
i am using this query
select min(checktime)::time as checkin,
max(checktime)::time as checkout,
(case
when cast(min (a.checktime) as time) > '11:00:00' then
(extract (epoch from min (a.checktime)::time - '11:00:00')/3600 )
else 0
end) as shorthour_in ,
(case
when cast(max (a.checktime) as time) <= '20:00:00'
then (extract (epoch from '20:00:00' - max (a.checktime)::time )/3600 )
else 0
end) as shorthour_out
from attendance_FHLHR
id checkin checkout shorthour_in shorthour_out
---------------------------------------------------------
1 12:27 18:10 1.45 1.83
2 11:00 19:59 0 0.016
can any one guide me is it correct query to get / calculate hours?
sql postgresql
sql postgresql
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
edited Nov 15 '18 at 7:08
a_horse_with_no_name
299k46458549
299k46458549
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
asked Nov 15 '18 at 7:03
Amna KalsoomAmna Kalsoom
367
367
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
1
What data type are the columnscheckinandcheckout? Thecast()seems to implicate you are storing atimevalue in avarcharcolumn.
– a_horse_with_no_name
Nov 15 '18 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09
|
show 3 more comments
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
1
What data type are the columnscheckinandcheckout? Thecast()seems to implicate you are storing atimevalue in avarcharcolumn.
– a_horse_with_no_name
Nov 15 '18 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
1
1
What data type are the columns
checkin and checkout? The cast() seems to implicate you are storing a time value in a varchar column.– a_horse_with_no_name
Nov 15 '18 at 7:08
What data type are the columns
checkin and checkout? The cast() seems to implicate you are storing a time value in a varchar column.– a_horse_with_no_name
Nov 15 '18 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09
|
show 3 more comments
1 Answer
1
active
oldest
votes
1
select min ( checktime )::time as checkin,
max (checktime)::time as checkout,
(case when cast(min (checktime) as time) >= '11:00:00' then
(extract (epoch from min (checktime)::time - '11:00:00')/3600 ) else 0 end) as shorthour_in ,
(case when cast(max ( checktime) as time) <= '20:00:00' then
(extract (epoch from '20:00:00' - max ( checktime)::time )/3600 ) else 0 end) as shorthour_out
from attendance_FHLHR
check this answer.
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
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
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%2f53314057%2fhow-to-calculate-short-hour-in-and-short-hour-out-in-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
select min ( checktime )::time as checkin,
max (checktime)::time as checkout,
(case when cast(min (checktime) as time) >= '11:00:00' then
(extract (epoch from min (checktime)::time - '11:00:00')/3600 ) else 0 end) as shorthour_in ,
(case when cast(max ( checktime) as time) <= '20:00:00' then
(extract (epoch from '20:00:00' - max ( checktime)::time )/3600 ) else 0 end) as shorthour_out
from attendance_FHLHR
check this answer.
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
add a comment |
1
select min ( checktime )::time as checkin,
max (checktime)::time as checkout,
(case when cast(min (checktime) as time) >= '11:00:00' then
(extract (epoch from min (checktime)::time - '11:00:00')/3600 ) else 0 end) as shorthour_in ,
(case when cast(max ( checktime) as time) <= '20:00:00' then
(extract (epoch from '20:00:00' - max ( checktime)::time )/3600 ) else 0 end) as shorthour_out
from attendance_FHLHR
check this answer.
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
add a comment |
1
1
1
select min ( checktime )::time as checkin,
max (checktime)::time as checkout,
(case when cast(min (checktime) as time) >= '11:00:00' then
(extract (epoch from min (checktime)::time - '11:00:00')/3600 ) else 0 end) as shorthour_in ,
(case when cast(max ( checktime) as time) <= '20:00:00' then
(extract (epoch from '20:00:00' - max ( checktime)::time )/3600 ) else 0 end) as shorthour_out
from attendance_FHLHR
check this answer.
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
select min ( checktime )::time as checkin,
max (checktime)::time as checkout,
(case when cast(min (checktime) as time) >= '11:00:00' then
(extract (epoch from min (checktime)::time - '11:00:00')/3600 ) else 0 end) as shorthour_in ,
(case when cast(max ( checktime) as time) <= '20:00:00' then
(extract (epoch from '20:00:00' - max ( checktime)::time )/3600 ) else 0 end) as shorthour_out
from attendance_FHLHR
check this answer.
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
answered Nov 15 '18 at 7:09
ZeshanZeshan
851210
851210
add a comment |
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
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%2f53314057%2fhow-to-calculate-short-hour-in-and-short-hour-out-in-postgresql%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
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 '18 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 '18 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 '18 at 7:07
1
What data type are the columns
checkinandcheckout? Thecast()seems to implicate you are storing atimevalue in avarcharcolumn.– a_horse_with_no_name
Nov 15 '18 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 '18 at 7:09