在php中同步管理员和客户端时间的最佳方法

Question

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?

Answer 1

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');

In sydney.php

SET time_zone = 'Australia/Sydney';
SELECT * FROM test_time;

In singapore.php

SET time_zone = 'Asia/Singapore';
SELECT * FROM test_time;

In ny.php

SET time_zone = 'America/New_York';
SELECT * FROM test_time;