SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    india
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    time difference sql query problem

    hello friends,

    i have a table called acc the structure is

    id integer primary key

    time datetime

    msg char(10)

    now i need to get the time from acc suchaway that

    where the time should be difference in minutes

    where msg = invite and msg = bye

    i.e bye time - invite time = our required time

    so how do i need to write the query iam trying with this

    select datediff("minute",NT1.StartTime, NT2.EndTime) as DiffTime from
    (select min(time) as StartTime from acc where msg='INVITE') NT1,
    (select max(time) as EndTime from acc where msg='BYE') NT2

    its giveing error as

    You have an error in your SQL syntax near '("minute",NT1.StartTime, NT2.EndTime) as DiffTime from
    (select min(time) as Sta' at line 1

    so please guide me

    with regards
    ramakanth

  2. #2
    SitePoint Guru dale_burrell's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    861
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not quite clear what you are attempting to do here, but there looks to be an error in your datdiff function... should be datediff(minute, ...) no quotes required last time I checked.
    If you aren't living life on the edge
    - you're taking up too much space
    Creative Dreaming Ltd / Ask The Local / Amanzi Travel

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    dale, nice try, but you're thinking of the sql server datediff function

    the mysql datediff function (added in 4.1.1) takes two dates

    ramakanth, use UNIX_TIMESTAMP functions to convert both datetimes into seconds, then subtract, then use SEC_TO_TIME to convert the result to HH:MM:SS format

    caution: if the difference is more than 1 full day, you will have to subtract multiples of 86400 first
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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