I am not sure if this is hard question or not, but I cannot figure out what is the best way or approach for this case.
I want to synchronize the time of my admin account of the system I am working now, to see the client time base on their time zone. And I has to be ALL the times. for instance I have an admin time of 1:20am in America/New_York time zone, and I will be viewing my client depending on their time zone, like 'Asia/Singapore' will be 1:20pm.
So 1:20am for admin, but admin viewing client will be 1:20pm.
What is the best way to convert those time depending on time zones, and sync it to client depending on client`s time zone.
I have sorted this out by making my function to convert it to client, BUT I will have to use that function to every datetime of my database.
What is the best way to do that?
Not sure how your server is setup.
If you use MYSQL, use TIMESTAMP type to collect date/time data. MySQL will record standard timestamp to database (regardless to the timezone as the world have one reference).
When query data, MySQL will automatically convert that data to server timezone before presenting you (Just the core setting when database server is setup).
you can try SET time_zone = timezone; function.
MySQL will temporary map new timezone based on your database connection session when you do another query. (e.g. single web page request)
For example, if you run the query immediately after open the database connection.
SET time_zone = 'Australia/Sydney';
All the SELECTED timestamp fields will be converted to Sydney timezone when you do the another query.(It will not effect another session even it's run at the same time. E.g. another PHP file without MySQL set_time_zone query)
Please note that it will not modify your field or core MySQL settings because it's will be real-time conversion based on session only.
================= EXAMPLE ===================
CREATE TABLE test_time (
user_id varchar(50) NOT NULL,
register_time timestamp NULL DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO test_time VALUES ('user1','2014-03-16 13:00:00'),('user2','2014-03-16 21:00:00');
SET time_zone = 'Australia/Sydney';
SELECT * FROM test_time;
SET time_zone = 'Asia/Singapore';
SELECT * FROM test_time;
SET time_zone = 'America/New_York';
SELECT * FROM test_time;
The safest way is to ask for user to input his timezone (for example when he registers to your site). If timezone is critical for your service you have to make sure that the client defines his timezone himself, and he accepts all damages may arise by failing to set his timezone by checking a checkbox or something.
If you dont want to ask the client for his timezone you can try to guess his timezone based on his ip using
via php: https://github.com/maxmind/GeoIP2-php
via js: https://bitbucket.org/pellepim/jstimezonedetect
In my experience, the safest way is to ask the user. Depending of the service you provide to the client, the desirable timezone isnt always the timezone of current location of the user.
For example, if i use a remote surveillance system, i could be in japan but i want to check my security cameras on my multibillionaire mansion in USA. So i need the service's time to know what time is in usa, not my time in japan.