We are facing timezone issues in our Laravel application and database setup.
We are located in +2 timezone and +2 timezone in out config/app => timezone, so all dates in the database are stored in +2 timezone. All models have fields created_at, updated_at and deleted_at that are in 2+ timezone We are working with payment API's and we need to handle datetimes retrieved from them. One of the services returns dates in UTC timezone, another one returns dates in +3 timezone. We faced issue on that step, because dates returned from services includes timezone data and they parsed to correctly by Laravel/Carbon, but when saving to the database, Laravel loses timezone information. So if it was 12:00:00+3, it'll be saved as 12:00:00, but when reading it, Carbon fills it with app timezone (+2), so we have 12:00:00+2
We are using MySQL and Laravel. My questions are: What is the best practice for managing datetime to ensure scalability and avoid issues with customers outside of the +2 timezone? If we use UTC timezone for the database, should we also use UTC as the app timezone? What is the best way to manage customer timezones to ensure accurate reports and payouts? What issues might arise from using either UTC or the +2 timezone?
We considering align all the dates in the database and I have an idea to create custom attribute cast that will call setTimezone before saving to the database and shiftTimezone after loading from the database. My concern here about what time zone should we use, +2 or UTC. If we save everything in UTC, it'll be good when we have customers in other than +2 timezones, but for now I want to be shure that all our logic keep working as expected if we changed timezone. I read some articles and discussions about timezones and mostly it's recomended to use UTC, but we have schedules that based on +2 timezone, Reports generation system, that should work correctly for daily reports, payouts and potentially there are more datetime related things.
