How do I compute Time

Question

0.00/5 (No votes)

See more:

Good day Im developing an attendance system with payroll, and im using a stand alone biometric device to get their time in and out, i'm already done in coding the software which gathers the data collected from the biometric device and save it to mysql database, my only concern is how can i compute the time span between time in and time out in mysql browser if the data that i get is exactly the same as this:

Time_in
2013-12-16 10:41:00

Time_out
2013-12-16 13:05:00

anyone knows how to compute time span in sql ? or anyone knows how to get the time in sql and transfer it in vb as datetimepicker value? i really need to compute the time between in and out to multiply it with the employee's basic salary

or anyone knows how to separate the date and time in sql? but the value will not change.

Im using visual studio 2010 ultimate and Mysql query browser 1.2.17 as backend database

Posted 31-Jan-14 18:51pm

Edwardine Ocampo

Updated 31-Jan-14 19:27pm

v2

Add a Solution

2 solutions

Solution 1

Try

SQL

SELECT
  TIMEDIFF(t1, t2) as diff
FROM tab

Posted 31-Jan-14 18:57pm

Abhinav S

Comments

Abhinav S 1-Feb-14 7:49am

Try http://www.linglom.com/programming/vb-net/accessing-mysql-on-vbnet-using-mysql-connectornet-part-i-introduction/.
Accept this answer if it helped.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-01-31T19:20:00

Solution 2

SQL

SELECT TIME_TO_SEC(TIMEDIFF(time_out, time_in)) timespan_in_second
FROM tablename

This will give the time lapse in second.

Posted 31-Jan-14 19:20pm

Peter Leow

Comments

Edwardine Ocampo 1-Feb-14 1:22am

same problem here i got 0 as result :(

Peter Leow 1-Feb-14 1:31am

try it at sqlfiddle: http://sqlfiddle.com/#!8/af297/3

Edwardine Ocampo 1-Feb-14 2:04am

yeah it works at sql fiddle but in my database its not working sir :(

Peter Leow 1-Feb-14 2:12am

This is a valid sql statement for mysql, check the documentation: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
We would not know what goes wrong on your machine.

Edwardine Ocampo 1-Feb-14 4:25am

thanks sir :), ill read the article

Edwardine Ocampo 1-Feb-14 7:19am

this also solve my problem could you teach me how to code it in vb 2010? and convert it to integer type for example 1800sec, in vb 2010 it should be 5hours? tnx in advance sir

i use this SELECT TIME_TO_SEC(TIMEDIFF(time_out, time_in)) timespan_in_second
FROM tablename