Issue in getting the max and min of date from one field

Hi...

I have a php code for importing attendance data to database.

I have here the sample data that was imported in database:

EMP_NO DATE_DTR DTR
9300127 2011-11-14 2011-11-14 05:35:00
9300127 2011-11-14 2011-11-14 13:35:00
9300127 2011-11-15 2011-11-15 13:35:00
9300127 2011-11-15 2011-11-15 21:35:00
9300127 2011-11-16 2011-11-16 21:35:00
9300127 2011-11-17 2011-11-17 05:35:00
9300127 2011-11-17 2011-11-17 21:35:00
9300127 2011-11-18 2011-11-18 05:35:00

As you can see I have 3 different shifts:

-- 05:35:00 - 13:35:00
-- 13:35:00 - 21:35:00
-- 21:35:00 - 05:35:00

because of the time in and timeout is in one field, i used the mysql function max and min to get time in and time out and I inserted it in different tables.

I used this code for inserting data with the time in and timeout is separately.

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr) 
SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.dtr)
FROM regular_dtr a
LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR = b.DATE_DTR)
GROUP BY a.EMP_NO, a.DATE_DTR;

Code (markup):

and the output of this code is:

EMP_NO DATE_DTR max_dtr min_dtr
9300127 2011-11-14 2011-11-14 13:35:00 2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00
9300127 2011-11-15 2011-11-15 21:35:00 2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00
9300127 2011-11-16 2011-11-16 21:35:00 2011-11-16 21:35:00 // wrong output
9300127 2011-11-17 2011-11-17 21:35:00 2011-11-17 05:35:00 //wrong output
9300127 2011-11-18 2011-11-18 05:35:00 2011-11-18 05:35:00 // wrong output


the max_dtr = time out of employee
the min_dtr = time in of employee

I want output is like this:

EMP_NO DATE_DTR max_dtr min_dtr
9300127 2011-11-14 2011-11-14 13:35:00 2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00
9300127 2011-11-15 2011-11-15 21:35:00 2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00
9300127 2011-11-16 2011-11-17 05:35:00 2011-11-16 21:35:00 // because it is from shift 21:35:00 TO 05:35:00
9300127 2011-11-17 2011-11-18 05:35:00 2011-11-17 21:35:00 // same with the above example with 21:35;00 - 05:35:00 shift.

As you noticed i have only problem in using max and min to get the timein and timeout in the shift for 21:35:00 - 05:35:00 because they are diiferent date.

I have no idea how can i resolved my problem in 21:35:00-05:35:00 shift. I don't know how can i revised my syntax that will not affect the two shifts that has a correct output.

Any help is highly appreciated and I want you to know that the reason why I posted my problem it's because its urgent and I'm not good in logic in mysql query and also i'm not familiar with other functions of mysql..

Again Thank you so much in advance.

If you have any questions for further understanding of my problem feel free to ask me..

Thank you

 

1) What is the data type of the fields? If it's not a calculable field, you can't use math functions (like max or min) on it.

2) You can't calculate from one clock time to an earlier time the next day. 2100 to 0500 is not a valid time span, since subtracting 2300 from 0500 gives a negative value. (You'd have to add 24 hours to the time the next day.) You'll need to store a Unix-type timestamp (which is the number of seconds since some point in the past) or an Excel-style timestamp (a real number made of the pseudo-Julian date, a decimal point and the portion of the day), or return a dataset of all the employee's records and use external code (PHP, probably) to calculate what you need.

 

Here is the datatype of my tables:EMP_NO - varcharDATE_DTR - DATEDTR - DATE/TIME-------------EMP_NO - varcharDATE_DTR - datemax_dtr - datetimemin_dtr - datetimehow can i used unixtimestamp in my code and also the php??

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr) SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.dtr)FROM regular_dtr aLEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR = b.DATE_DTR)GROUP BY a.EMP_NO, a.DATE_DTR;

Code (markup):

Thank you so much

 

A Unix timestamp is is an integer in the database. In PHP you use getTimestamp() to get the timestamp from the date/time. The nice thing is that the unix timestamp for 0500 minus the unix timestamp for 2300 of the pervious day is 28800 seconds, or 8 hours. As long as you don't fall below 0, or climb above maxint, it works, regardless of days, months, years, etc.

 

Hi..Can you give an example so i could understand more on how dows the unixtimestamp will help me to solve my issue.Thank you

 

I only want to know is what can i do?what is the syntax if i only have data EMP_NO and Daily_Time_Record which mix check in and check out and also theirs a scenario that the employee check in twice or check out twice or sometimes no checkin or no check out.It's hard for me to figured out how can I get the date in check in, and the check in and checkout of an employee. Like i've said before i used min and max and i found out I have problem if the shift or his Daily_Time_Record is 2011-11-21 21:35:00 - 2011-11-22 05:35:00 the date is different...I don't have problem if the schedule is 2011-11-20 05:35:00 - 2011-11-20 13:35:00My head was crushing i don't know what to do..what the syntax is.. :-[If it is php code or pure mysql and how.. Thank you for your help..