MIgrating MySQL query to Postgres

Question

I have a MySQL query which works perfectly but now we are migrating to Postgres and its not working because of the variables used in MySQL. The query is as follows:

SELECT COUNT
    (
        ((
                speed / 3.6 - prevspeed / 3.6 
            ) / TIMESTAMPDIFF ( SECOND, prevdatenew, servertime )) / 9.80665 
    ) AS hardAccels 
FROM
    (
    SELECT ID
        ,
        servertime,
        @prevDateNew AS prevdatenew,
        @prevDateNew := servertime,
        speed,
        @prevSpeed AS prevspeed,
        @prevSpeed := speed 
    FROM
        tc_positions 
    WHERE
        deviceid = 32 
        AND ` ID ` BETWEEN 1318429 
        AND 1322230 
    ) t1 

WHERE
    ((
        speed / 3.6 - prevspeed / 3.6 
    ) / TIMESTAMPDIFF ( SECOND, prevdatenew, servertime )) / 9.80665 > 0.255;

This returns an error because it does not recognize the variables used in mysql:

ERROR:  syntax error at or near ":="
LINE 9:                @prevDateNew:=servertime,
                                   ^

Any ideas to resolve this?

Pavel Stehule · Accepted Answer · 2019-11-02 06:40:37Z

2

You are using variables for access to values on previous rows. For PostgreSQL you should to use ANSI SQL standard function lag. This function is a window function, so you have to specify order. I have not a your data, your table, so following query is just for inspiration

SELECT speed - lag(speed,1) OVER (ORDER BY id) /
       EXTRACT(epoch FROM (
            servertime - lag(servertime, 1) OVER (ORDER BY id))) 
  FROM tc_positions
   ...

Nice article about this topic is http://www.postgresqltutorial.com/postgresql-window-function/

answered Nov 2, 2019 at 6:40

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MIgrating MySQL query to Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related