MYSQL Time stamp difference

Archived Topics
1

I am trying to make a time clock within Ignition. I have a table that stores the employee clock number and the time they punch in, and the time they punch out. I am trying to get the difference in hours:minutes:seconds between these two fields.

I have looked at :

Datediff()
Datesub()
SubTime()

And I can not get it to work. Do those values have to be converted to subtract out?

Any ideas?

2

Here is an example using MySQL’s TIMESTAMPDIFF and SEC_TO_TIME functions:

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2010-04-09 02:02:56', '2010-04-09 03:25:32')); This returns 01:22:36.

The functions are documented at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html.

3

Here is the code I had:

create view punchclock as select id_employee, clockin, clockout, SEC_TO_TIME(TIMESTAMPDIFF(SECOND,clockin,clockout)) as mytime from tbl_timeclock where clockout is not null

It returned output formatted like this:
1970-01-01 01:21:34

So I tryed this:

create view punchclock as select id_employee, clockin, clockout, DATE_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,clockin,clockout)),'%H:%i:%s') as mytime from tbl_timeclock where clockout is not null

This returned either of these two:

00:00:00 or NULL

I am stumped

4

MySql is unique in that it allows 0 as a time. ie: 00/00/0000 00:00:00

This can give some very funny (in a bad way) results when you start doing time math. Ensure that the time you are using are real time and not 0.

0 can get in there if you define your column as NOT NULL DEFAULT 0.

5

My column is set to allow nulls, with default null.

The above example code was ran against the same data with those results. I am confused how it could calculate with the first code and show nulls in the second.

6

The problem is because you are using a view. If you take that query out of the view and just run it the results should be just fine. However, the view is taking the final result thinking it is a date but it is a string. So in the view you can cast it to a string:create view punchclock as select id_employee, clockin, clockout, CAST(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,clockin,clockout)) as CHAR) as mytime from tbl_timeclock where clockout is not null

7

You could try using TIMEDIFF(), it returns the results in the format you’re looking for, makes the query easier to read. Works on time and date-time values, both values have to be the same type.

SELECT TIMEDIFF('2010-04-10 03:25:32', '2010-04-09 02:02:56');