Javascript Disabled Detected

You currently have javascript disabled. Several functions may not work. Please re-enable javascript to access full functionality.

Problem in calculating time difference from one column with datatype datetime

Started by newphpcoder, Nov 22 2011 05:29 PM

Please log in to reply

13 replies to this topic

#1
newphpcoder

Posted 22 November 2011 - 05:29 PM

Programming Professional

Members
474 posts

Good day!

I got a problem in my query syntax to get the total hours per day based on their time in and time out...

Here is the scenario:

the employee sometimes they swipe their id twice to Time In or to Time OUT, so I used syntax max in timeout and min in time in.

Here is the sample DTR data from the database:
EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 PM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 PM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 PM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 PM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

I used this syntax to get the timedifference per day/employee:



INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))

FROM regular_dtr a

LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO GROUP BY a.EMP_NO;

the result in this query is:
EMP_NO TotalHours
300395 296:00:00
9300127 296:00:00

I want output is:

EMP_NO TotalHours
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00

I search in internet fot the right syntax, i tried time_to_sec, DATEDIFF, sec_to_time, but still wrong input, I post my problem in forum because I need to solved it. And I need help..

Thank you so much..

#2
codehutch

Posted 22 November 2011 - 05:59 PM

Newbie

Members
13 posts

I think, it sums up the all of the hours because you group it by employee #. Did you try adding another col to Group By? Try date (formatted to "MM/dd/yyyy", or any kind as long as you don't include the time).

#3
newphpcoder

Posted 22 November 2011 - 06:54 PM

Programming Professional

Members
474 posts

Thank you for your bright idea, it works, but a little bit problem, because, I extra data with 00:00:00 output and also two output become 00:00:00 but it should 08:00:00.

Actually, the output will be 24 rows only, but it becomes 26 rows and the 4 rows has 00:00:00 output.

here is my new code and the data output:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))

FROM regular_dtr a

LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE b.DATE_DTR = a.DATE_DTR AND a.EMP_NO = b.EMP_NO GROUP BY a.DATE_DTR,  a.EMP_NO;

DTR DATA:

Quote

EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 AM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 AM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 AM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 AM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

#4
codehutch

Posted 22 November 2011 - 07:50 PM

Newbie

Members
13 posts

Hey,

I tried a query on MSSQL [if you're using MySQL, this might at least give you an idea].

I created two tables for Time-in (empDTR) and Time-out (empDTR2). Here's the query:

SELECT a.empNo, DateDiff(HOUR, a.time,b.time) as totalHours, b.time,a.time

FROM empDTR a,empDTR2 b

WHERE CONVERT(varchar(8), a.time, 112) = CONVERT(varchar(8), b.time, 112) AND a.empNo = b.empNo 

GROUP BY a.empNo,a.time,b.time

result
[ATTACH=CONFIG]4334[/ATTACH]

If you're using MySQL and having trouble implementing it, let me know.

=========================
Online Code Repository

Edited by codehutch, 22 November 2011 - 10:21 PM.

#5
newphpcoder

Posted 22 November 2011 - 09:10 PM

Programming Professional

Members
474 posts

Okay, I will try it, but I have new query:
but a little bit problem, because, I extra data with 00:00:00 output and also two output become 00:00:00 but it should 08:00:00.

Actually, the output will be 24 rows only, but it becomes 26 rows and the 4 rows has 00:00:00 output.

here is my new code and the data output:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))

FROM regular_dtr a

LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE b.DATE_DTR = a.DATE_DTR AND a.EMP_NO = b.EMP_NO GROUP BY a.DATE_DTR,  a.EMP_NO;

DTR DATA:

Quote

I have 12days for 300395 and also 12 days for 9300127

And data out in database using this new query is:

300395 00:00:00
9300127 00:00:00
300395 08:00:00
9300127 08:00:00 // 11 output like this, it should be 12 output like this and no output 00:00:00
300395 00:00:00
9300127 00:00:00

total output is 26 rows

thank you so much

---------- Post added at 05:10 AM ---------- Previous post was at 05:08 AM ----------

Sorry, but I could not separate my timein and timeout because it is only importing from the system of DTR.

Thank you for your help

#6
codehutch

Posted 22 November 2011 - 09:17 PM

Newbie

Members
13 posts

How do you detect if it's a time-in or time-out? can you give me the structure of your dtr table?

#7
newphpcoder

Posted 22 November 2011 - 09:30 PM

Programming Professional

Members
474 posts

Nothing? But I used max and min...

I have no choice but to used their format...Actually I am the one who adjust..

Thank you

#8
codehutch

Posted 22 November 2011 - 09:41 PM

Newbie

Members
13 posts

ok. I made a little tweak. same result. just added few more filters

SELECT a.empNo, DateDiff(HOUR, a.time,b.time) as totalHours, b.time,a.time

FROM DTR a, DTR b

WHERE CONVERT(varchar(8), a.time, 112) = CONVERT(varchar(8), b.time, 112) 

and a.empNo = b.empNo 

and a.time <> b.time 

and b.time > a.time 

GROUP BY a.empNo,a.time,b.time

filters:
must have same day

CONVERT(varchar(8), a.time, 112) = CONVERT(varchar(8), b.time, 112)

time must be different [don't take the difference of "2011-11-03 05:35:00.000" and "2011-11-03 05:35:00.000"]

a.time <> b.time

eliminate negatives [one must be greater than the other]

b.time > a.time

I'm not saying that it is optimized. Just here to give you some ideas.

=========================
Online Code Repository

Edited by codehutch, 22 November 2011 - 10:21 PM.

#9
newphpcoder

Posted 22 November 2011 - 09:58 PM

Programming Professional

Members
474 posts

When I tried this code:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, DateDiff(HOUR, a.DTR,b.DTR) as totalHours, b.DTR,a.DTR

FROM regular_dtr a

LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE CONVERT(varchar(8), a.DTR, 112) = CONVERT(varchar(8), b.DTR, 112) 

and a.EMP_NO = b.EMP_NO 

and a.DTR <> b.DTR 

and b.DTR > b.DTR 

GROUP BY a.EMP_NO,a.DTR,b.DTR;

I got an error:

Error Code : 1582
Incorrect parameter count in the call to native function 'DateDiff'
(0 ms taken)

Thank you

#10
codehutch

Posted 22 November 2011 - 10:08 PM

Newbie

Members
13 posts

My code is for MSSQL. Are you using MySQL? If yes, DateDiff only requires two parameters. But I think in this case you need to use TimeDiff of MySQL [google it up for usage]. I can't shift to my other OS right now, so I cannot double check on my side. MSSQL and MySQL might have different syntax but as long as you have the idea, it's easy to convert between the two.

#11
newphpcoder

Posted 22 November 2011 - 10:23 PM

Programming Professional

Members
474 posts

Did you read my thread # 5?

That is my previous code.

Thank you

#12
codehutch

Posted 22 November 2011 - 10:28 PM

Newbie

Members
13 posts

Yes, I have. That's why I'm asking what DB you are using. since the error


Error Code : 1582

Incorrect parameter count in the call to native function 'DateDiff'

(0 ms taken)

is obviously a mismatch in number of parameters in DateDiff

Back to Database & Database Programming

1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users

Problem in calculating time difference from one column with datatype datetime

#1 newphpcoder Posted 22 November 2011 - 05:29 PM

#2 codehutch Posted 22 November 2011 - 05:59 PM

#3 newphpcoder Posted 22 November 2011 - 06:54 PM

#4 codehutch Posted 22 November 2011 - 07:50 PM

#5 newphpcoder Posted 22 November 2011 - 09:10 PM

#6 codehutch Posted 22 November 2011 - 09:17 PM

#7 newphpcoder Posted 22 November 2011 - 09:30 PM

#8 codehutch Posted 22 November 2011 - 09:41 PM

#9 newphpcoder Posted 22 November 2011 - 09:58 PM

#10 codehutch Posted 22 November 2011 - 10:08 PM

#11 newphpcoder Posted 22 November 2011 - 10:23 PM

#12 codehutch Posted 22 November 2011 - 10:28 PM

1 user(s) are reading this topic

Sign In

#1
newphpcoder

Posted 22 November 2011 - 05:29 PM

#2
codehutch

Posted 22 November 2011 - 05:59 PM

#3
newphpcoder

Posted 22 November 2011 - 06:54 PM

#4
codehutch

Posted 22 November 2011 - 07:50 PM

#5
newphpcoder

Posted 22 November 2011 - 09:10 PM

#6
codehutch

Posted 22 November 2011 - 09:17 PM

#7
newphpcoder

Posted 22 November 2011 - 09:30 PM

#8
codehutch

Posted 22 November 2011 - 09:41 PM

#9
newphpcoder

Posted 22 November 2011 - 09:58 PM

#10
codehutch

Posted 22 November 2011 - 10:08 PM

#11
newphpcoder

Posted 22 November 2011 - 10:23 PM

#12
codehutch

Posted 22 November 2011 - 10:28 PM