Cannot get my head around the best way to do this.
I have multiple clients in different time zones
I currently store date/times in UTC and format the sql with what the real time is for the client based on their timezone at the moment they make a request.
Problem with this is when the timezone changes (i.e. Summer time/Winter time) this won't work if the client requests data for the previous day then I would be calculating the date/time of the record based on the new time which could be out by 1 hour.
So what do people suggest is the best method for saving the date/time?
Put the actual time based on their timezone in the db and also use UTC for a base reference?
Or something else....?