CODEWITHSUNDEEP
phpmysqlselecttimezone
Adam Tal
Adam Tal

Reputation: 921

Select from mysql by day with different timezones (php)

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, yesterday), 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 target range (yesterday).

What would be the best way to do that?

Upvotes: 4

Views: 1752

Answers (1)

VolkerK
VolkerK

Reputation: 96149

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', 'PST8PDT')
  AND dt < CONVERT_TZ(CURDATE(), 'EST', 'PST8PDT')+Interval 1 day

Upvotes: 2

Related Questions