Find user by the time of the day (timezone)

I am using Drupal and I changed the way the timezone field is used in the users table. It used to contain the number of seconds difference. For example when my timezone was EST (America/Montreal), then this field would have -18000 as the value.

-5 * 60 * 60 == -18000

Now, this field contains the actual timezone id (America/Montreal or EST). This is much more precise and support daylight savings.

I needed to find all users which their clock is 5AM in order to send them a daily newsletter. But those loving at the other side of the globe would have a different timezone and therefor would get their newsletter at 5PM their time if we sent the newsletter at 5AM Eastern time.

SELECT uid, name, timezone FROM users
WHERE HOUR(CONVERT_TZ(NOW(), @@session.time_zone, IFNULL(timezone, 'GMT'))) = 5;

@@session.time_zone contains the server's timezone for this session.
CONVERT_TZ() converts the given date/time from one timezone to another.

So I am converting the current time from the current timezone to the user's timezone and make sure it's 5 o'clock.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote>
  • You may post PHP code. You should include <?php ?> tags.
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options