0

I'm trying to alter a select statement to account for the users local timezone, but I'm unable to wrap my head around it.

It's suppose to select any records from the current day in the users timezone, in this case it's hard coded to 4 hours.

Currently I'm using...

SELECT * FROM {$this->burnsTable} WHERE DATE(created) = DATE(NOW() - INTERVAL 4 HOUR)

I want to alter that statement to use convert_tz, is that possible?

1 Answers1

1

MySQL isn't going to know anything about your users' time zones. You'll need to get it with javascript or potentially PHP, though the best approach is often to just ask the user to select one. In that case, you can store their choice in the database, and then MySQL can know their time zone. You could then use convert_tz (or simply populate the offset you give to your ADDTIME call). If you've set up your database with the time zone strings then you can store those and use them with convert_tz, or you can still use convert_tz with offsets by doing something like

SELECT * FROM {$this->burnsTable} WHERE DATE(CONVERT_TZ(created,'+00:00','+4:00')) = CURDATE();

You can join to the users table to do this, but instead of doing that on what could potentially be many queries it might be better to have the time zone stored in your PHP user model, and then just pass the appropriate offset instead of having to look it up each time.

Community
  • 1
  • 1
A C
  • 705
  • 6
  • 9
  • Sorry I didn't make that part clear, I ***know*** their timezone. It was more about the ability to select the record from the current date with regards to their own timezone. This looks like what I need. –  Feb 16 '17 at 18:25