?
Solved

get difference in  time from 2 timestamps

Posted on 2009-02-12
3
Medium Priority
?
397 Views
Last Modified: 2013-12-12
I have 2 timestamp columns in a db table. One is TimeIn the other is TimeOut.

What I'm trying to do is find the total time, i.e. the difference between the 2 columns. What I thought would work is just subtract the 2 but I get a weird responses.

Here's my query:
SELECT
TimeOut,
TimeIn,
(TimeOut-TimeIn) as TT
FROM
project_time

But what I get is the following:
2/12/2009 10:37:40 | 2/12/2009 10:37:30 | 10
2/12/2009 10:39:21 | 2/12/2009 10:37:21 | 200
2/12/2009 10:40:00 | 2/12/2009 10:39:30 | 70
2/12/2009 10:53:00 | 2/12/2009 10:52:00 | 100
2/12/2009 11:06:28 | 2/12/2009 10:57:36 | 4892
2/12/2009 11:08:04 | 2/12/2009 11:06:49 | 155

What I want is the total Seconds. From there I can compute the Min & Hours. I've also tried both Timestamp and Datetime but both had the same results.
0
Comment
Question by:phper
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 23624748
Which version of mysql?

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff


SELECT
TimeOut,
TimeIn,
TIMEDIFF(TimeIn,TimeOut) as TT
FROM
project_time

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23624863
You could also transform the datetime columns to a unix timestamp, and subtract:
SELECT
TimeOut,
TimeIn,
unix_timestamp(TimeOut)-
unix_timestamp(TimeIn) as TT
FROM
project_time

Open in new window

0
 

Author Closing Comment

by:phper
ID: 31546195
It's
TIMEDIFF(TimeOut,TimeIn) as TT

But you got it, thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month17 days, 6 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question