I'm currently working on an application that stores doctor appointments on a weekday basis. To manage this, we have a schedule table and a calendar table that stores the dates. Below is a snapshot of the schedule table: https://i.stack.imgur.com/OPRPb.png
Within our application, when a user selects a date, the list of doctor's appointments for that specific day is displayed based on the day name. Currently, I am extracting the necessary data range wise using the query below, without considering time zones:
select datefield as date,
(select count(id)
from appointment_schedules
where substring(dayname(calendar.datefield),1,3) = appointment_schedules.week_day
and user_id=$user_id
)
from calendar
where datefield between '$start_date' and '$end_date'"
We are now working on implementing a timezone-based appointment list display, which will show appointments based on the user's local timezone. Since weekdays are fixed in the schedule table, I am facing challenges in retrieving accurate data considering different timezones. For instance, if there is an 8-hour difference between the user's timezone and the server, the user's day may be ahead or behind the server's day within a certain period. I am struggling to figure out how to display user timezone-specific day-wise data accurately with a single query.
I would greatly appreciate any suggestions or guidance on this matter. Thank you in advance.