0

If a user sets a reminder notification time exactly at 8 AM, he should get the notification exactly at 8 AM even after he moved to a different timezone.

I mean if he moved to any other country with different timezone even after that he should get the notification exactly at 8 AM on the morning of that country.

To send notifications, I'm using CRON which is running in UTC timezone.

Consider this example, user's reminder time is 8 AM in IST (+5:30), I'm storing 2:30 in UTC into the database. But what if he moved to EDT (-4:00) timezone area? But also I have a huge amount of data of the user.

Please suggest the solution considering that I have to perform that operation using PHP and the database is MySQL.

Pradeep
  • 9,667
  • 13
  • 27
  • 34
vishal
  • 104
  • 13
  • 3
    The time in the database should be saved in UTC. Then you can have extra column with the offset. Then you can choose if you want to fetch the time in UTC or by the users timezone (the offset). That way, you only need to update the offset for the user in the database. Some examples: https://stackoverflow.com/questions/15017799/how-to-convert-utc-date-to-local-time-zone-in-mysql-select-query – M. Eriksson Apr 27 '18 at 11:12
  • I already have user's timezone. I'm also updating the timezone if he moved to a different timezone. – vishal Apr 27 '18 at 11:15
  • If all the dates are stored as UTC, then check the link I posted to. – M. Eriksson Apr 27 '18 at 11:16
  • You didn't got what is the problem here. Consider this example, user's reminder time is 8 AM in IST (+5:30), I'm storing 2:30 in UTC into the database. If he moved to EDT (-4:00) timezone area and I'm going to convert the stored time (2:30) to user's new timezone (-4:00), I'll get 10:30 PM of the previous day. Which is not correct. It should be 8 AM, not 10:30 PM on the previous day. And don't forget I'm using CRON Jobs to send notifications. – vishal Apr 27 '18 at 11:20
  • I don't think you understood what I meant: If the user wants to get a notification at 8 am (no matter the timezone), store 8 am UTC in the database. Then, when you do your query, you compare the stored notification date (converted to the users timezone) with the current local time (in the users timezone). That way, it will always notify you at 8 am in the users local timezone. – M. Eriksson Apr 27 '18 at 11:38
  • What is CRON ? (I've heard of cron - but that is a package - only specific components of which can be "run") – symcbean Apr 27 '18 at 11:48
  • Follow these links https://en.wikipedia.org/wiki/Cron and http://coderssecret.com/how-to-write-cron-jobs-on-aws-ec2-instance/ – vishal Apr 27 '18 at 11:55
  • @MagnusEriksson I'm using CRON to send notifications and the server is in UTC. I can't store the time directly. Please tell me if you got this or want me to explain. – vishal Apr 27 '18 at 12:04
  • _"with the current local time (in the users timezone)"_ = The systems current time (as you stated was UTC), converted to the users timezone. It doesn't matter where you run the script. – M. Eriksson Apr 27 '18 at 12:07
  • This works fine if a user stays in a single timezone but what if he changed the timezone? – vishal Apr 27 '18 at 12:10
  • The user obviously needs to update his/hers timezone in your database somehow. It's pretty impossible for you for your script to magically know what timezone the user is in otherwise. If the user updates the timezone, then _that_ timezone will be used in your query instead. It might help us to know more about what this is. App? Web site? Send notifications through mail? How does the user define the timezone to begin with? – M. Eriksson Apr 27 '18 at 12:14
  • You still didn't get the situation here. I have user's timezone (timezone is getting updated when the app detects the changes in the timezone). If he moved to a different timezone, I'm updating the existing timezone with this new one but the problem is with the data of the user which is already there in the database. Check above example. It's an app by the way and CRON is sending push notifications. – vishal Apr 27 '18 at 12:25

1 Answers1

0

I would perform all my notifying operations on the server via CRON. Lets imagine a simple example:

1) You live in a Country A which has a GMT+3 timezone. It is now 14:00 GMT+3. You have a meeting in Country B which is under GMT+1.

2) Your meeting in this country is at 16:00 GMT+1 or 18:00 GMT+3. So 4 more hours until the meeting.

3) You set up an appointment in your application for a meeting in 18:00 GMT+3. All appointments in your server are saved as GMT+0, thus this appointment is saved as 15:00 GMT+0.

4) Let us imagine you teleport to the country of the meeting and the time is now 12:00 GMT+1. After 4 hours have passed (16:00 GMT+1, 18:00 GMT+3, 15:00 GMT+0), you will still be notified by the server, regardless of your change in timezone. Since the server will have your appointment saved as GMT+0 and will be checking with a CRON job every, lets say 5 minutes`, if someone's appointment is due.

On the other hand, if you wanna perform the check on the client, you should always transform the client's datetime to whatever format is saved on the server and check with the server.

Bill Souvas
  • 46
  • 2
  • 6
  • The CRON is checking the current GMT time and matches with users reminder time using MySQL query. And also this notification contains some information of the user by fetching data from MySQL relational database. The problem is with existing users data. It is still the same with respect to country A and we have lots of users data. If I perform MySQL join operations or some other conditions which require the time of the user then we'll always get the wrong result. – vishal Apr 27 '18 at 11:46