Globally Offsetting Times

[MSRez]

I've just had a server move for a website and unfortunately the time of the new server is GMT -6 whereas the old server was GMT so all the times stored in the database are wrong when displayed on the site.

Is there any way I can globally offset all times across the site so that the times are their original GMT values?

 

[DRJ478]

There is this example in the user contributed comments in the PHP manual:

print("server timezone is: " . getenv('TZ') . "<br>\n");
print("server time is: " . date("H:i:s") . "<br><br>\n");
print("changing server time zone to US/Pacific....<br><br>\n");
putenv("TZ=US/Pacific");
print("new server time is: " . date("H:i:s") . "<br>\n");
print("new server timezone for this script is: " . getenv('TZ'));

some Unix servers may use a different format for TZ. Example: putenv("TZ=PST8EDT");

 

[MSRez]

Thanks, works brilliantly and just what I was after.

 

[Olavxxx]

Hi,

I made two mysql tables in one db, where one is the countrycodes (iso3166) and one is for timezones.

You can then run a natural join on country and timezones.
if > 1 result, show availible timezones in that country
else set timezone to users country

You could define a standard-timezone, but also let your users choose theire country upon registration.

You would then run the join on three tables:
user, iso3166 and tbl_timezone

SELECT TZ FROM user, iso3166, tbl_timezone WHERE user.country = iso_3166_country.iso3166_country AND tbl_timezone.country_code = iso3166_country.iso3166_a2;

ps. I do it like this:
Upon login (when you ahve to query the user anyways, I check if the field TZ in the users table is set.
If it's not set, I run the query above.. (theire country must be set!).

If the query gets one row, I set the field TZ to that TZ result. If it gets more, I populate an listbox and let the user choose his/her region in his/her country.

Then, I use the putenv("TZ=...
But, the mysql is not affected by this!

This means that you have to either run addtime, or something in the mysql db, or do it in php.

I think doing it inside mysql is faster, so I went for that option. You can use addtime (I think it was that I used), and if you have 23:00 and add 2hrs, it automagically finds next day at 01:00.

You can also add negative integers!

How do you know what time to add?
You can use the difference between the original system time and the timezone you used in putenv. (simple math, in other words).

It's very easy actually, though I used many hours thinking about it, before I stumbled across the iso3166 and timezones.

They where not in database-format, so I built the databases up and tested.
It works great!

Since it's so easy, it's more work for me to give the php-source, rather than you making it on your own!

It's just some simple queries, and adding of one or two fields to your users table, also one very simple form for user input, if more than one timezone is availible in that country.


Good luck!

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster