I was using PHPRunner with PostgreSQL and `timestamp with time zone` (`timestamptz`) variable type, and I noticed the following issue.
Although my PostgreSQL database (in Azure) was configured to use the Europe/London timezone, within my PHPRunner application the default value for an updated field was set to now() and rather than showing my present BST time it was showing UTC values on ADD or EDIT of the form – this was resulting in the user observing UTC time in the updated and created fields at record add or edit.
—
The Cause
After investigation I discovered that because PHPRunner evaluates now() on the PHP application server when the Add or Edit page is generated. If the PHP runtime is using UTC, the value inserted into in my case the Updated field was also UTC, that wasn’t what I was wanting. I was wanting Europe/London time (with automated adjustment for BST) visible to the user in the UI prior to it being saved to the database.
Interestingly clicking the *Now* button within the DateTime control displayed the correct local time. It turned out this was because that value is generated by JavaScript running in the user’s browser.
In other words:
* PHPRunner now() → PHP server time
* Now button → Browser local time
* PostgreSQL `timestamptz` → Database session time
If these three are not aligned, you can end up with timestamps that differ.
—
The Solution
My PostgreSQL server was already configured to the correct timezone (in my case `Europe/London` in Postgres flexible server for Azure). I also needed to configure PHP to use the same timezone. This can be done as follows;
In Global Events → After Application Initialized, add:
date_default_timezone_set("Europe/London");
Now the PHPRunner `now()` function should generate values using the London timezone, including automatic transitions between GMT and BST.
—
Why This Is Better
Many developers choose to let PostgreSQL generate audit timestamps such as `created` and `updated`. While that works well, I prefer using PHPRunner’s `now()` for these fields.
The advantage is that users can immediately see the timestamp that will be written to the database before they save the record. It also provides a consistent experience between Add and Edit pages.
—
Final Thoughts
The important thing is to keep all three layers using the same timezone:
* PHP
* PostgreSQL
* The user’s browser
Once PHP is configured with:
date_default_timezone_set("Europe/London");
PHPRunner’s now() behaves exactly as expected and remains in sync with PostgreSQL timestamptz values and the browser’s Now button.