Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Unanswered: Substract datetime rows with where clause

    Hi there.

    I have a table where are stored some values such as:

    Id, Mobile, Speed, DateTime
    175080, 1886, 40, 2012-10-03 08:07:56
    175081, 2470, 75, 2012-10-03 08:08:48
    175082, 1886, 0, 2012-10-03 08:10:59
    175083, 1886, 20, 2012-10-03 08:12:59

    And I used to use a query for substracting these dates and then get the difference between two dates:

    SELECT f.id, f.mobile, f.DateTime as StardDate, f2.DateTime as EndDate,
    (f.DateTime -f2.DateTime) AS difference
    FROM table f, table f2
    where f2.id = f.id-1
    and f.mobile=1886
    order by f.DateTime desc

    The problem is that previously I only have one mobile and now I have more.

    How can I just substract the dates of the mobile 1886 ?

    Best regards.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this idea of joining on id = id - 1 is flawed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    so, how can I do the substract then?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I had written a small white paper on how this can be done using user session variables.

    MySQL – get time difference between successive records IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •