date_default_timezone_set() Question

Hello:

As far as I know, the time setting is done by JavaScript, consulting the "Timezone" of the browser.

Read this article: Intl.DateTimeFormat

Greetings,
fernando

I was facing the same dilemma last few days. My challenge was to have the web application that supports people from different timezones and able to show thier local timezone correctly.

Here's the final approach i have taken. There are 2 important aspects to handle the data/time and timezone when your web has audiences from different timezones:

1. How you store date/time in your DB.
2. How you present the date to user.

In General, if you serve audiences with different timezones:

1. Always set your Server OS and MySQL Timezone to GMT+0
2. Store your date/time in GMT+0, (If your OS and MySQL Servers are in GMT+0, it will help here).
3. Show/Retrieve date time in users' timezone with the help of thier profile preference data, or browser detection.

Your question: So if I store a date time entry using now(), should it input the server time (UTC) or the adjusted time from the date_default_timezone_set()?

When you store date/time, store as UTC (GMT+0). In your case, your server OS and MySQL(?) Timezones are already in UTC, so you just need to save directly as per normal. But, you have altered the timezone at app init, which is in America/New_York timezone, this changes the date time from UTC to be stored as America/New_York local time, and also display as well.

It is better you check the 3 Timezones (OS, MySQL, PHP) settings first as shown in steps below. When you retrieve and show the data, you adjust the timezone according to users' timezone, as shown in the steps below.

Here are the details:

To be sure, confirm 3 timezone settings first:

a. Your Server OS timezone (Affects PHP date() command and other defaults)
b. MySQL timezone settings. (Affect NOW(), MySQL query that deals with dates)
c. PHP ini Timezone (Affects the date and time commands and thus final values stores in the DB).

1. The Server OS Timezone. To confirm its timezone, for Linux Ubuntu:

Note: This server is in GMT+8 timezone

2. Timezone Settings in MySQL (This affects timestamp data type in your DB and NOW() , dated related query etc. )

Check MySQL Server Timezone:

3. Check PHP ini Timezone:
   echo date_default_timezone_get();

Result: Asia/Singapore

In the above case, the server default was installed with GMT+8, because of where it is located, and MySQL timezone is "+8", Whenever a user query update a date with now() as data, the final date will be GMT + 8 hours, in YY-MM-DD HH:MM:SS format and stored into the MySQL DB field with datetime type. (This will be confusing to people from other timezone!)

So we need to let PHP takes full control of the date and timezone at the application level (i.e. independant of and not affected by Server OS and MySQL timezone Settings). The approach is as follows:

1. Saving Data/time: Always make sure to store date/time in GMT+0 into your MySQL Database.

Preparation of Date/Time data in PHP (For Server OS and MySQL default timezone not already in GMT+0):

1. Before saving the data, force all date and time to GMT+ 0 (Skip this if your OS Timezone in GMT+0 and PHP timezone default are already GMT+0 )
   date_default_timezone_set('UTC');

   
   

2. Format the date/time value before Update DB:
   $datetime = date('Y-m-d H:i:s');

   
   

3. Then issue DB API Query Command:

   
   

"UPDATE Table SET my_datetime = '$datetime' ";

Note: It seems that now() is a PHPRunner specific function and it is the same as PHP's date('Y-m-d H:i:s').
So $datetime = date('Y-m-d H:i:s'); is the same as $datetime = now();

Note: MySQL's NOW() function in the query statement, it will be affected by MySQL's Timezone settings:
"UPDATE Table SET my_datetime = NOW() " will have different result from "UPDATE Table SET my_datetime = '$datetime' ";
if MySQL's timezone is NOT in UTC and if PHP is in UTC as per above example.

If your Server OS and MySQL DB Timezones are both already in GMT+0 (UTC), congrats! you can just update the DB as per normal, without chnaging the timezone, using any of the above query statements.

2. Displaying Date/Time: Always let PHP to fully control the presentation of the date and time to users, and independant of OS and MySQL Timezone settings:

After user logs in, if user's timezone setting in his/her profile is "America/New_York", or detect his/her browser is from "America/New_York", place the code at Before Display event of the page where you want to show date:

date_default_timezone_set("America/New_York");

The above will adjust the UTC date/time saved in the DB to be automatically presented with the local date/time of the users.

It is better not to place at app_init, which will affect both List page and Add/Edit Page. In your case, you let everyone see New York timezone, which is not an issue, what if you decide to allow different users view the dates based on their own local time zones later, and as a result, saved the dates with their local timezones?

This will give you a messy table if your users are in different timezones, where your date/time data saved becomes inconsistent. Think, different people in different timezones need to setup a meeting, each will stores their local dates into your DB and everyone misses the meeting. When they contact you, you can't tell the actual dates looking at the stored data!

So, this is why always store dates in UTC, and show dates in user local time zone.

There are many ways to do that, this is my implemantation approach which is easier for me to manage.

Hope it helps, also document my experience here to remind myself!

@acpan, thank you for such a detailed write-up. I just want to provide a quick summary.

There are two ways to save the datetime values in the database, using MySQL default values and using PHP. In case of MySQL the timezone will be taken from the server time settings. Usually you cannot control this unless this is your own server. Using PHP to save datetime values is more flexible and you can control the timezone by using date_default_timezone_set();

In PHP you can use one of two options to populate datetime fields. First option is to use now() as a default value of the field. And a second - use code like this in BeforeAdd/BeforeEdit events:

$values["FieldNameHere"] = now();