Hello,
We are using postgres as our database and where there are DATETIME columns they have a type of "TIMESTAMP WITHOUT TIME ZONE", and when a time is put into an SQL query (lib/srdb1/db_ut.c:231) we use localtime() (not gmtime()). For many applications this will be OK as we are always comparing localtime, but it does change the unix epoch. If I want to store 0 seconds, here in the UK it comes out as "1970-01-01 01:00:00" 1 hour ahead of UTC.
This has an effect on the location table which uses an expires timestamp of "0" to mean permanent. Could this have an effect on other modules comparing time from the database, or situations where geographically distributed systems (in different timezones) are accessing the same central database?
Hugh
On Thursday 01 March 2012, Hugh Waite wrote:
We are using postgres as our database and where there are DATETIME columns they have a type of "TIMESTAMP WITHOUT TIME ZONE", and when a time is put into an SQL query (lib/srdb1/db_ut.c:231) we use localtime() (not gmtime()). For many applications this will be OK as we are always comparing localtime, but it does change the unix epoch. If I want to store 0 seconds, here in the UK it comes out as "1970-01-01 01:00:00" 1 hour ahead of UTC.
This has an effect on the location table which uses an expires timestamp of "0" to mean permanent. Could this have an effect on other modules comparing time from the database, or situations where geographically distributed systems (in different timezones) are accessing the same central database?
Hi Hugh,
I think it has also an effect to the p_usrloc module, which uses a certain timestamp value in the failover column which is regularly checked and set to the default if has been changed.
Another candidate is maybe billing, if you use something inside the server from the acc module, I'd also check this.
Viele Grüße/ best regards,
Henning Westerholt
-
Henning Westerholt -
Hugh Waite