Choose from mysql by day with different time zones (php)

Question

I store links in a database, and each of them has a date and time field with a date and time in a PST based database.

I want my user to be able to display all records from a specific date (e.g. today, yesterday) and select a time zone.

eg. if I want to see all the records that were created yesterday in the EST time zone, I need to first convert (or read) all datetime values ​​to EST, and then select only those in the target range (yesterday).

What would be the best way to do this?

Answer 1

MySQL Convert_TZ (dt, from_tz, to_tz).

.

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

2010-03-20 10:00:00

edit: , " EST", -

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