[RESOLVED] Crazy MySQL when it comes to Timezones

[peter_schneider]

Hello,
I need MySQL to run different timezones than the system depending on the location of the user. that's why I can't change MySQL's global time but I can set each user's MySQL-session time at the beginning of the script like that:

(1.)
SET time_zone = 'Europe/Helsinki'

Now here comes the crazyness: After I've done that this is what happens in the script:

(2.)
SELECT NOW()
//outputs the user's correct "timezone" time 🙂

(3.)
UPDATE ...
SET logindate = NOW()
WHERE ...
//instead of the user's correct "timezone" puts the system time!!! in the db cell! 🙁

(4.)
SELECT NOW()
//outputs the user's correct "timezone" time again :o

How can I get UPDATE's NOW() to put the correct "timezone" time in the db cell???

[peter_schneider]

I found a solution that works for me:

The affected database cell was formatted 'timestamp'/CURRENT_TIMESTAMP which resulted in a current timestamp(of the system time) on update no matter what the update was 😉

What I did: I reformated the database cell to varchar(19) which is just enough space to hold that timestamp deliverred by now(). 'now' the desired date ends up in the database cell. :evilgrin:

[Weedpacket]

Why not use a proper date/time type rather than a string?

[peter_schneider]

of course. i changed that. thanks. 🆒