0

I wanted to move some logic from my php code to my mysql queries which involved making time and date calculations which need always to be in reference to UTC.

In the php I have been using ini_set('date.timezone', 'UTC'); in my functions include to guarantee consistency.

As I may NOT always have control over the system timezone I have set the time zone per connection using SET time_zone = 'UTC'; which will ensure that anywhere where a derived date is used within the MySQL queries it will be UTC.

However what is not clear to me from the documentation nor from excellent answer by T J Crowder to a similar question is whether this has an impact on

ADDDATE() ADDTIME() or DATE_ADD()

i.e. Do these account for day light saving based on the session timezone or do these account for daylight saving regardless.

In the event of the latter - Is it possible to CAST the result of say an ADDTIME() calculation using CONVERT_TZ(dt,from_tz,to_tz) where we derive the from_tz from the MySQL system tables.

Note : - we know that @@global.time_zone, @@session.time_zone return 'SYSTEM' in the absence of a set timezone so can't be used in the convert.

Community
  • 1
  • 1
codepuppy
  • 1,130
  • 2
  • 15
  • 25
  • What do you really want to achieve? Adjust your db record timings to support day light savings(not possible, but there are workarounds) or do you want to follow UTC always? (that will do without you doing much anyway)...? – bonCodigo Jan 14 '13 at 10:56
  • Yes I want to always use UTC. The documentation is specific in reference to functions where the date is derived. i.e. It will take the session time zone. But does not mention what timezone is applied to increments. Hence the question to get clarification as to whether I need to do anything to ensure UTC consistently applied. – codepuppy Jan 14 '13 at 11:05
  • Like the edit @duffymo - oops. – codepuppy Jan 14 '13 at 11:11

0 Answers0