I have a site which makes heavy use of dates. The site has an NZ audience but is hosted with MediaTemple in the US. I needed to make it work so that dates were both stored in the database and displayed in the New Zealand time zone. All seems fine so far, so I am sharing how I went about this. (This is for SS 2.3.1 - I haven't tested on other versions).
As I cannot change the time on the server itself, I had to change the time zone in two places:
Luckily this web host allows you to add in PHP configuration directives into the .htaccess file, so I added this to the end of the site's .htaccess file:
### SET TIMEZONE ###
SetEnv TZ Pacific/Auckland
A full list of php time zones can be found here: http://www.php.net/manual/en/timezones.php
I would also advise viewing the output of a phpinfo() call (and checking what is output under the Date header) to double check it has been set correctly for your site. You could also check the output of something like "echo date("l, F d, Y H:i" ,time());"
This is slightly trickier if you don't have access to the MySQL server config (and I am assuming you don't). What you essentially need to do is set the timezone on a per connection basis before any other queries are made to the database. Unfortunately in this case, this means editing the following file: sapphire/core/model/MySQLDatabase.php
As this is a core file, be aware that changes incorrectly made could break your site, and if you upgrade silverstripe, it will be overwritten. I have submitted an bugfix with this change: http://open.silverstripe.com/ticket/3910
At line 41 underneath this line:
$this->database = $parameters['database'];
mysql_query("SET SESSION time_zone='" . $parameters['timezone'] . "'");
Then in my _config.php file, I added the following line to my $databaseConfig:
"timezone" => "+12:00",
so a sample $databaseConfig looked like this:
$databaseConfig = array(
"type" => "MySQLDatabase",
"server" => "localhost",
"username" => "test",
"password" => "",
"database" => "test",
"timezone" => "+12:00",
Setting the time zone numerically can cause issues with daylight savings, but there is no viable alternative unless time zones have been loaded into the MySQL server, and you will not know this unless you have full config control over your database.
Applying the above changes should hopefully allow you to work with dates and times in a time zone of your choosing regardless of where your server is located.