ERROR: column "minute" does not exist

Question

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.

Answer 1

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