0

I'm stuck in this logic. I'm working on a message scheduling project and I have clients from different countries. Let's consider Asia (does not observe daylight saving) and America/New York (observes daylight saving).

Now, i'm writing following query to get schedules within 10 min gap from current time:

select message,subject,person_or_group,customer_id from common_schedule where reminder_type_1 in('beforedays','beforehours') and now() between cast(reminder_time1 as datetime) and addtime(cast(reminder_time1 as datetime),'00:10:00')

Now, if we have two records, one for New York (under daylight saving) and one for New Delhi: India that is not under daylight saving, I'll get wrong data for New York as 1 hour should be subtracted from it.

Also assume that there is an extra column in the table DY (daylight saving) which has values 'y' or 'n'.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ashutosh
  • 4,371
  • 10
  • 59
  • 105

1 Answers1

2

A few things:

  • When you use MYSQL's now() function (docs here), the value is returned in the current time zone.

  • The time zone can be set either global for the system, or per-connection to the database (docs here).

  • You can use MYSQL's CONVERT_TZ function (docs here) to convert from one time zone to another, or between a local time zone and UTC.

  • A boolean flag for DST is never a good idea because countries around the world observe DST at different points in time. Additionally, countries often change their rules about when to observe DST. All of the different rules are included in the time zone data, which you are referencing when you use a time zone like America/New_York.

  • For future scheduling of events, you should probably store both the local time of the event and the time zone that it pertains to.

  • Depending on the volume of data you're working with, you might consider a strategy that pre-calculates a UTC time to run from the local time it was scheduled at. Then you can much easier locate the next tasks to run, by comparing the UTC "now" against the pre-calculated column. However, you should be prepared to reassess the UTC equivalents periodically, or at least as often as you receive updates to time zone data.

  • Scheduling of future events is inherently difficult. See also previous posts I've written on this subject: here, here, here and here.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575