Calculate actual time from different timezones in php

Question

Hai,

I would like to calculate actual time from different timezones. I have two mysql tables

1.Timezone table: This table contians two fields (offset value, country name) values are ( -12:00, Eniwetok, Kwajalein) (-11:00, Midway Island, Samoa).... so on

2.My employee table This table contains the employees details with two field names (created_on, timezone) and the values are (current timestamp, -11:00) (current timestamp, -12:00)..... so on

Now, If my employee logged in to my system, i want to display the time according to their timezones values from tables.

Basically if my Current timestamp is :2010-05-10 15:41:39 offset values is: +5:30

my result should be : 10 May 2010 3:41pm

the same for different offset values for the same current timestamp. I believe i explained the issue correctly, if you want any information, please ask me.

Answer 1

I believe what you're looking for is date_default_timezone_set()

date_default_timezone_set() sets the default timezone used by all date/time functions

Answer 2

1. Use timestamp datatype to store all dates in your application
2. for each employee keep it's timezone in format of timezone name. Ex: "Asia/Vladivostok"
3. After employee is identified just after mysql_connect - send yet another query to specify which timezone is used now. This can be done with mysql query SET time_zone=TZ, where TZ const is time shift like "+10:00" or if you're lucky and your mysql server has timezones collection imported - more correct way like: "Asia/Vladivostok" (SET time_zone="Asia/Vladivostok")
4. Also - don't forget to specify php's timezone with ini_set('date.timezone', TZ) or already mentioned date_default_timezone_set()

Answer 3

You seem to have an underlying problem with your data.

The employee table contains fixed timezone offsets for past events - presumably when the profiles were created - but these can't necessarily be used to determine offsets for future events, as many locations use daylight saving time (DST).

The timezone table seems redundant at the moment, as there is no way you can cross-reference against employees. And again, offsets will change due to DST.

You would normally store the location for each user in your database, then use something like date_default_timezone_set() that to display times correctly for that location.