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

    Unanswered: timediff mysql query to postgres

    Hello

    I have the next query in mysql to get the time difference from two rows, but I would like to implement it in postgres, but I'm getting some errors when I try to query it.

    select speed,mobile,date,
    timediff(@nexttime,date) as tdiff2, (@nexttime:=date) as Next
    from positions
    where speed=0 and mobile = 1883 and date >(now() - interval 2 hour)

    I have changed the last part to: (now() - interval '2 hours') but I go on getting another error in the Timediff command.

    Can anyone help me please?

    Best regards.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And the errors are?

    (@nexttime:=date) as Next
    Is not valid Postgres syntax. What is that supposed to do?

    What's that @nexttime supposed to do?

    And please use [code] text to format your SQL statements and make them better readable.

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    What I want to do is something like this:

    Date Timediff (result)
    14-12-2010 20:25:40 00:01:05
    14-12-2010 20:26:45 00:00:15
    14:12-2010 20:27:00 ......
    .....
    ...

    So, the result would be: the substract of the dates to get how much time have spent from the last date

    The errors is that the function timediff doesnt exist in postgres.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by franrtorres77 View Post
    So, the result would be: the substract of the dates to get how much time have spent from the last date
    Code:
    SELECT date1 - date2
    FROM your_table

  5. #5
    Join Date
    Nov 2010
    Posts
    9
    but, how do I substract the bellow row with the upper row?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT the_column_with_no_name - lag(the_column_with_no_name) over (order by the_column_with_no_name asc)
    FROM your_table_without_name
    For future posts please remember:

    1) post the full definition of your table as a CREATE TABLE statement
    2) post sample data as INSERT INTO statements
    3) post the expected result from the sample data

    this helps us finding a proper solution for your problem

  7. #7
    Join Date
    Nov 2010
    Posts
    9
    It works! thank you very much! I will have in mind your recommendatios for the next posts.

    Thank you again!

Posting Permissions

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