How to manage requests from different time-zones in PHP

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 )
LVL 32
Marco GasiFreelancerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

An Average Forum Participant Just For FunHardware Tester and DebuggerCommented:
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.
Julian HansenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marco GasiFreelancerAuthor 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).

Julian HansenCommented:
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 GasiFreelancerAuthor 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 :)
Julian HansenCommented:
Sorry Marco, not taking the time to read properly - sounds like you are on top of it though.
Marco GasiFreelancerAuthor Commented:
Thank you guys. Julian solutions is the best to solve my issue. Thank yo again.
Julian HansenCommented:
You are welcome Marco,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mobile

From novice to tech pro — start learning today.