Select from mysql by day with different timezones (php)

Question

I'm storing leads in a database, and each lead has a datetime field with a PST timezone based date & time.

I want my user to be able to display all leads from a certain date (e.g. today, yseterday), and choose the timezone.

E.g. if I want to see all leads that were generated yesterday in EST timezone, I need to first convert (or read) all the datetime values to EST, and then only select those who are in the right range (yesterday).

What would be the best way to do that?

Answer 1

You can use MySQL's Convert_TZ(dt, from_tz, to_tz) function.

E.g.

SELECT CONVERT_TZ('2010-03-20 12:00:00', 'EST', 'PST8PDT')

returns 2010-03-20 10:00:00

edit: TO select all records that are "today in EST" you can use something like

SELECT
  x,y,z
FROM
  foo
WHERE
  dt >= CONVERT_TZ(CURDATE(), 'EST')
  AND dt < CONVERT_TZ(CURDATE(), 'EST')+Interval 1 day