MySql set session timezone

[aljaz86]

Hi,

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?

[Jon]

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?

[aljaz86]

Hey,

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(a.id) as count
FROM table1 a
JOIN table2 f on f.id = 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

[aljaz86]

I actually managed to solve the problem differently with using CONVERT_TZ

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