0

I work on an existing project that stores dates to a MySQL DB. The dates are stored as UTC since all users so far were in GTM+0 and no conversion was needed.

I now need to modify the code so that users from other time zones can use the system. The users choose their timezone when they register to the system, so I have a table holding the timezone for each user.

I know I can use CONVERT_TZ() when I extract and store the dates, but to do so means to go through all the queries and add this function.

When I do: SET @@session.time_zone:='+7:00'; select now(); The result changes with the timezone variable.

When I do: SET @@session.time_zone:='+7:00'; select myDate from myTable; The result stays the same, returning what is stored in the DB.

Is there any way I can change the connection string or is there a session variable I can use that will affect the queries without having to add CONVERT_TZ to every single query?

Edit: this is not a duplicate of Should I use field 'datetime' or 'timestamp'? since using timestamp means I need to change all the date field in the DB, while I try to change something more global so I will not have to do massive changes the Db fields or the code.

Community
  • 1
  • 1
OliverK
  • 1
  • 3
  • 1
    Possible duplicate of [Should I use field 'datetime' or 'timestamp'?](http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp) – Jocelyn Jun 07 '16 at 06:42
  • The 2nd answer to the question linked in my previous comment is probably what you need. – Jocelyn Jun 07 '16 at 06:46
  • Thank you @Jocelyn , it's a better solution than changing the code, although it means I need to change all DateTime columns in all the tables. Also, TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC, making it unusable for some scenarios. – OliverK Jun 07 '16 at 13:48
  • The duplicate link is actually an answer to your question. If you used timestamps, then setting the session level timezone would provide you with a solution. With datetime data type, it will not. You either convert your data type or your queries. Your pick. – Shadow Jun 08 '16 at 01:43
  • I guess I have no option other than to change my code (we have dates prior to 1970-01-01). Thank you both. – OliverK Jun 08 '16 at 09:15

0 Answers0