MySql set session timezone


I have a specific question with the MySQL node. I am trying to loop through different environments on our DB that have different timezone settings. I was trying to add this expression
SET SESSION time_zone = (SELECT value FROM xxxxx WHERE key = ‘timezone’);
at the start of the query but it throws me an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DATE(a.completed) as completed, f.tit at line 2

Any ideas?

Hey @aljaz86,

It looks like there might be more to your query that we are not seeing, Have you tried manually running the query that the expression builds to see if it works?


yes, if I run it on the DB it works just fine. Also, if I remove the part of setting the session timezone it also executes well.

Here is the full querry

SET SESSION time_zone = (SELECT `value` FROM `settings_table` WHERE `key` = 'timezone');

SELECT DATE(a.completed) as completed, f.title,a.type, count( as count
FROM table1 a
JOIN table2 f on = a.id_pr
where  a.id_state >3 and a.priority < 9 and a.deleted is null
GROUP BY DATE(a.completed)
ORDER  BY DATE(a.completed) ASC

I actually managed to solve the problem differently with using CONVERT_TZ

CONVERT_TZ(a.created, @@session.time_zone, '+04:00') AS coverted_datetime
FROM table1 a
where = 1
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.