-1

I am migration mysql to Postgres

Runing this bellow query on both Mysql and Postgres

SELECT 
cb_sessions.*,cb_users.username,
cb_users.userid,cb_users.email 
FROM cb_sessions 
LEFT JOIN (cb_users) ON (cb_sessions.session_user=cb_users.userid) 
WHERE 
TIMESTAMPDIFF(MINUTE,cb_sessions.last_active,'2018-06-21 18:55:14') < 6

Get Successfully results from Mysql but in Postgres through error:

ERROR: column "minute" does not exist LINE 1: ...session_user=cb_users.userid) WHERE TIMESTAMPDIFF(MINUTE,cb_...

why getting an error in postgres and is the solution.

Basically, the issue with TIMESTAMPDIFF I think so.

  • 1
    WHERE cb_sessions.last_active < '2018-06-21 18:55:14' + INTERVAL 6 MINUTE has a better change of being optimized. – Rick James Jun 22 '18 at 0:10
2

TIMESTAMPDIFF does not exist in PostgreSQL.

Rewrite your query as such:

WHERE cb_sessions.last_acive - '2018-06-21 18:55:14'::timestamp < '6 minutes'::interval

Be wary however of timezones...

See also this same question: https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql

And PostgreSQL documenation for date/time/interval functions: https://www.postgresql.org/docs/current/static/functions-datetime.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.