How to calculate short hour in and short hour out in postgresql?
up vote
0
down vote
favorite
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 at 7:08
a_horse_with_no_name
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
|
show 3 more comments
up vote
0
down vote
favorite
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 at 7:08
a_horse_with_no_name
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 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 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09
|
show 3 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 7:08
a_horse_with_no_name
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
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 at 7:08
a_horse_with_no_name
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
edited Nov 15 at 7:08
a_horse_with_no_name
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
edited Nov 15 at 7:08
a_horse_with_no_name
289k46438533
edited Nov 15 at 7:08
a_horse_with_no_name
289k46438533
edited Nov 15 at 7:08
a_horse_with_no_name
289k46438533
289k46438533
asked Nov 15 at 7:03
Amna Kalsoom
276
asked Nov 15 at 7:03
Amna Kalsoom
276
asked Nov 15 at 7:03
Amna Kalsoom
276
276
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 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 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09
|
show 3 more comments
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 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 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 at 7:04
Properly formatted SQL is so much easier to read and understand.
– jarlh
Nov 15 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 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 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 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09
|
show 3 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
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 at 7:09
Zeshan
381110
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
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 at 7:09
Zeshan
381110
add a comment |
up vote
1
down vote
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 at 7:09
Zeshan
381110
add a comment |
up vote
1
down vote
up vote
1
down vote
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 at 7:09
Zeshan
381110
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 at 7:09
Zeshan
381110
answered Nov 15 at 7:09
Zeshan
381110
answered Nov 15 at 7:09
Zeshan
381110
answered Nov 15 at 7:09
Zeshan
381110
381110
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.
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.
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 at 7:04
Please share a sample input and expected output.
– Mayank Porwal
Nov 15 at 7:07
@MayankPorwal i write expected output. see question completely
– Amna Kalsoom
Nov 15 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 at 7:08
And what is the input for this output?
– Mayank Porwal
Nov 15 at 7:09