Does SQLAlchemy have support for user-defined variables?
https://dev.mysql.com/doc/refman/5.6/en/user-variables.html
Problem: I am trying to get the time difference between two consecutive rows. I can do this by writing raw SQL query using user-defined variables. Is this possible to do in SQLAlchemy without writing raw SQL query?
My table is something like this:
id user_id date
1. user_1 01-11-2021 00:00
2. user_1 01-11-2021 00:00
3. user_1 01-11-2021 00:01
4. user_2 01-11-2021 00:00
Output would be something like
id user_id time_diff
1. user_1 NULL
2. user_1 0
3. user_1 1
4. user_2 NULL
The SQL query which performs needed task and does not use UDV and is applicable in MySQL 5.6 looks like:
SELECT t1.*, TIMESTAMPDIFF(SECOND, t1.date, t2.date) time_diff
FROM table t1
LEFT JOIN table t2 ON t1.user_id = t2.user_id
AND t1.date > t2.date
LEFT JOIN table t3 ON t1.user_id = t3.user_id
AND t1.date > t3.date
AND t3.date > t2.date
WHERE t3.id IS NULL
The logic: we select 2 rows (by date value) for a user from table copies t1 and t2 (and row in t2 have earlier date value), and the copy t3 checks that these rows are really adjacent (there is no third row between them).
This query does not use UDV and can be converted to SQLAlchemy syntax.