Server timezone

[Chris]

Is there some way to change timezone in your script without changing the timezone of the server?
(like: sever is on GMT+5 and I want the script work as if it was GMT+0)

It can be done via a dirty hack, I don't mind

[Nox]

date_default_timezone_set()

[Chris]

Is there a way to change the timezone of MySQL too (philosophical note: just a moment ago I would be happy if just PHP time() and date() were working with this but once I got it it's "no, not enough, need more", this is soo greedy )?


To understand how it works:
* It seems that time() gives seconds since start of UNIX era (1970) on GMT+0. And timezone setting does not affect that number. Right?
* date() takes raw time and apply the timezone to it.
* MySQL store DATE as integer identical to UNIX time, but since it returns the field in the date() like format it applies timezone to it (and it is the timezone of MySQL server, not PHP timezone).

So, if we store date&time as UNIX timestamp only (PHP only, no MySQL used) we can change timzeone on the fly, nothing will get broken?

[Nox]

I don't think so, imho
* it wouldn't be really useful if it didn't...
* it is only a formatting
* http://dev.mysql.com/doc/refman/5.5/en/datetime.html "The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'." => not a unix timestamp

[Chris]

time()

Code: [Select]

date_default_timezone_set("Europe/London"); echo date_default_timezone_get(); echo ' - '.time().' - '.date("H:i:s"); echo'<br>';
date_default_timezone_set("Europe/Paris"); echo date_default_timezone_get(); echo ' - '.time().' - '.date("H:i:s"); echo'<br>';
date_default_timezone_set("America/New_York"); echo date_default_timezone_get(); echo ' - '.time().' - '.date("H:i:s"); echo'<br>';returns:

Europe/London - 1309525211 - 14:00:11
Europe/Paris - 1309525211 - 15:00:11
America/New_York - 1309525211 - 09:00:11

It definitely looks like timezone is applied on date() formatting step.

[aquilax]

To change the timezone in MySql use:

Code: [Select]

SET time_zone = timezone;
to change it for the current session http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html .

For me it did't work with the timezone code ex. EET but GMT+2 got the job done.

[Chris]

So, we can change MySQL server timezone or current connection timezone, but we can't change specific database timezone?

Interesting thing, there is no such thing as permanent timezone. Most/all countries change timezone during summer and winter So Greenwich is not GMT+0, it is GMT+0 or GMT+1 depending on day of the year
Originally, I wanted to set timezone to GMT+0 so everyone could calculate form it their local time. But it would be wrong half of the year... Therefore for a consistent timezone I would have to use Europe/London (which is +0 or +1).
Another thing, since the timezone changes on all computers every year, it can't be hardcoded (into UNIX timestamp and database DATE/TIMESTAMP field types), it would force recalculation and rewrite of all these fields each year on all computers...

For the DATETIME and DATE range descriptions, “supported” means that although earlier values might work, there is no guarantee.

As for "The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.", supported means it is guaranteed to work, in fact you can use bigger ranges, so it must be stored as something like 64bit version of UNIX timestamp I guess...

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

Also this. Seems like everything is stored as true UTC internally.