CODEWITHSUNDEEP
phpmysqldatetimetime
Jason
Jason

Reputation: 33

How to compare datetime and time in phpmysql

Just one question: Is it possible to compare the datetime format and time format?

I have table employee_attendance (datetime type) and table employee_schedule (time type) and this is to determine IF the employee was late.

please check out my code.

$CheckInNew  = strtotime('2013-06-05 08:47:53');
$OnDutyTimeNew   = strtotime('08:00:00');

if($CheckIn > $OnDutyTimeNew){

// confirming that the employee is late

} else {

// confirming that the employee is NOT late

}

Any ideas?

Thank you

Upvotes: 1

Views: 141

Answers (5)

John Conde
John Conde

Reputation: 219794

You can do this in SQL:

-- checkin is the column with their checkin time
-- scheduled is the column with their scheduled time
SELECT
    TIMEDIFF(TIME(checkin), scheduled) AS diff

If diff is negative, then they're on time. If it is a positive number they are late

Reference

Upvotes: 0

invisal
invisal

Reputation: 11171

Since the date format contains leading zero, you can simply use pure string compare to compare the date.

$checkInDateTime  = '2013-06-05 08:47:53';
$dutyTime = '08:00:00';

if (substr($checkInDateTime, -8) > $dutyTime) {
    echo "Late";
} else {
    echo "Early";
}

Normally, a single day divides into 1,440 minutes. Swatch Internet time divides a single day into 1000 beat. Therefore 1 beat = 1.44 minutes. Using Swatch Internet time to compare a normal time is not accurate because it does not include seconds and there is minute error (at least this is what I believe).

Upvotes: 0

edwardmp
edwardmp

Reputation: 6591

Assuming you want to compare to the same date as the CheckInDateOnly date.

You could do this by converting the datetime to a date only, and adding that to the OnDutyTime variable.

$checkInDateTime = strtotime('2013-06-05 08:47:53');
$checkInDateOnly = date("Y-m-d", $checkInDateTime);
$OnDutyTimeNewWithSameDateAsCheckIn = strtotime($checkInDateOnly . '08:00:00');

echo ($checkInDateTime > $OnDutyTimeNewWithSameDateAsCheckIn ? "Late" : "Early");

Usage example: PHPFiddle

Upvotes: 0

Mark
Mark

Reputation: 8431

try this:

$CheckInNew  = date('B',strtotime('2013-06-05 08:47:53'));
$OnDutyTimeNew   = date('B',strtotime('08:00:00'));

if ($CheckInNew > $OnDutyTimeNew)
    echo 'late!';
else
    echo 'early!';

I use be as keyword for Internet Swatch Time.. For more info, here's the link

UPDATE: PHPFiddle

Upvotes: 2

Juned Ahsan
Juned Ahsan

Reputation: 68715

Why not parse the time in to a timestamp, then just compare using function strtotime()

Upvotes: 0

Related Questions