CODEWITHSUNDEEP
javascriptphpmysql
Sebastian Farham
Sebastian Farham

Reputation: 825

How to get elapsed time between a javascript Date function and a database date?

I have short script to store employee clock in and clock out time in database.

markup

     <input id='clocked_in_time' name='clocked_in_time' value='' type='text'>
<input id='clocked_out_time' name='clocked_out_time' value='' type='text'>

script

<script>
document.getElementById('clocked_in_time').value = Date();
document.getElementById('clocked_out_time').value = Date();
</script>

output

Fri Mar 17 2017 22:21:01 GMT-0400 (Eastern Standard Time)

The output is fine because that is exactly how my client wants to see it as it is international. But the problem now I need to calculate the difference between clock in time and clock out time.

One thing I have tried is to stock getHours(), getMinutes() and getSeconds() in separate columns in the database as follow: enter image description here

Then client-side do something like this:

<?=$attendance['timeouthour'] - $attendance['timeinhour'];?>

While this works, I need to get more specific and calculate minutes and seconds all together... How can I accomplish this? Or is there an easier way to go about solving this?

Upvotes: 2

Views: 170

Answers (1)

CodeGodie
CodeGodie

Reputation: 12132

Change your database columns so that they are of DATETIME type. Store your information so that they are of DATETIME, then you can make the comparison in the backend, use PHP to retrieve information from Mysql using this query:

SELECT TIMEDIFF(time_out, time_in) AS Duration
FROM my_table

Once retrieved, change the format of the date to match your user's expectations.

Upvotes: 1

Related Questions