How to manage requests from different time-zones in PHP

Marco Gasi
Marco Gasi used Ask the Experts™
on
Hi everybody.
I'm working on the back-end of an Android native mobile app. This app will be used to create items, send and accept requests from other user and so on.
I'm wondering how to manage the time zones with these requests. If a Japanese user sends a request to a Spanish user, both users will have to see in their app the correct date and time for their timezones and the date and time will be different...
How can I manage this issue in the back-end?
I guess I should save date and time with the server time-zone (just adding a column timestamp with default value set to NOW); then when data are requested, get the time-zone the request comes from, take the timestamp from the db and convert it to the target time zone.
But I'm not sure if this is the better way to solve the problem and how to do it.

Any help will be appreciated )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger
Commented:
I'd propose something similar:

1. Store all the dates in mysql timestamp column type
2. Right after connect to mysql - specify current user's timezone with SET time_zone='<tz>' where <tz> can be either +01:00 offset or timezone name Asia/Vladivostok (for the latter special timezones db should be imported by DBA)

The benefit of using timestamp is that it automatically converts the date to and from UTC, according to the time_zone you've specified in the current connection.

More details about timestamp: http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

About retrieving current user's timezone (even though there are a lot of answers here on SO about this topic, let's do that once again):

1. As long as there is a TZ value in cookies (look at step 4) - use it.
2. For registered/authenticated users - you can ask to specify their timezone and store it permanently in DB.
3. For guests you can retrieve the timezone from javascript (Date.getTimezoneOffset()) and send it with ajax
4. If it is the first request/nothing passed from ajax - guess it by IP
5. For each step (1-3) - save timezone to a cookie.
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
What if a user moves between timezones - if you are storing the user's timezone then things will get out of wack.

Best approach is
a) Store all date/time in UTC format on the server
b) Convert to local time on the client

From this PAQ
<script>
// TIME RETURNED AS ISO 8601
var d = '2018-04-24T16:52:48.000Z';

// MAKE A DATE OBJECT
var dt = new Date(d);

// OUTPUT IN LOCAL TIME
console.log(dt.toString());
</script>

Open in new window

Marco GasiFreelancer
Top Expert 2010

Author

Commented:
Hi guys.
Thank you for your answers. It looks that the Julian's thought about users moving between timezones is right.
So, Julian, if I correctly understand, I can just use in the DB a column of type TIMESTAMP and set its default to NOW; then ask the app developers to convert the timestamp returned in local datetime using Java functions (I have no control on the frontend code and they are not using javascript, just Java).
I'll check this with them and I'll come back here.
I'll leave the question open for the moment, just in case they have some objection.
Thank you again.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Java is server side - the nice thing with JavaScript is that you can tap into the User's locale settings so you will get the time they are expecting to see. If they move and they change their locale settings - the time will be automatically set to the new timezone.

If you don't have client access then not sure how you are going to get their timezone information short of doing an IP lookup. Under some pressure right now so there may be a solution here that I am not seeing but as far as I know this information is not available server side unless you put some script on the client to tell it.
Marco GasiFreelancer
Top Expert 2010

Author

Commented:
Julian, Java is the language to develop Android applications, besides the rest. Here I'm talking about an Android applications developed within AndroidStudio, which uses Java: no Javascript, no Ajax. But the app runs locally, in the phone: it is the client and it can access to phone data like system date and time.
They already said they think it will be easy and I'm just waiting for a confirmation to award the points.
Thank you again :)
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sorry Marco, not taking the time to read properly - sounds like you are on top of it though.
Marco GasiFreelancer
Top Expert 2010

Author

Commented:
Thank you guys. Julian solutions is the best to solve my issue. Thank yo again.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome Marco,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial