Datediff replacement in MySQL

[QatQat]

Hi there,

here is my table
create table dailyregister (
recordid int,
SignedIn datetime,
SignedOut datetime
TotalTime ???)


TotalTime is calculated upon request of the operator and it should be equal to subtracting SIgnedOut - SignedIn.

What datatype is best suited for the job? I tried datetime as well but I only get 0000-00-00 00:00:00 results.

I need to get the time difference between those two fields (in seconds or minutes) and I found out that datediff ()function only works in MySQL 4.1.

How do I go around?

Thank you

QatQat


Life is what happens when you are making other plans.

 

[rac2]

In my opinion the best way to store moments such as SignedIn and SignedOut is as DATETIME values. These can be massaged as needed for display or calculation.

I think the late appearance of DATEDIFF() in MySQL is an oddity; we always need to calculate duration.

Possibly UNIX_TIMESTAMP( date ) can be used

SELECT UNIX_TIMESTAMP( SignedOut ) - UNIX_TIMESTAMP( SignedIn ) AS "SecondsOnDuty"

But see further

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

 

[QatQat]

Thanks a lot;

that did it.


CHeers

QatQat

Life is what happens when you are making other plans.