=# show timezone; TimeZone ----------- localtime (1 row)
On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/24/2015 06:42 AM, Willy-Bas Loos wrote: > >> Hi, >> >> We're upgrading a database from 8.4 to 9.4 >> The web developer complains that the timestamps are suddenly 2 hours >> late. We are in GMT+02. >> The issue would go away if we cast the postgres timestamps to timestamp >> WITH timezone. It works in pg8.4 and 9.4 >> >> He told me that PHP always uses timezones, so i tried to reproduce it >> without the application layer. >> Since PHP always uses a timezone, the first part of the query always >> converts to "with time zone', it is what i presume PHP is doing. >> > > That is the same as assuming and I would verify. > > >> select timestamp with time zone 'epoch' + extract(epoch from >> now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3) >> --> 02:00:00 9.4 >> --> 00:00:00 8.4 >> >> select timestamp with time zone 'epoch' + extract(epoch from >> now()::timestamp WITH TIME ZONE) * interval '1 second' - >> now(),substr(version(), 12, 3) >> --> 00:00:00 9.4 >> --> 00:00:00 8.4 >> > > What does: > > show timezone; > > return? > > >> Is there a reason for this change of behavior between 8.4 and 9.* ? >> > > Have you looked at what TimeZone is set to in the 8.4 and 9.4 > postgresql.conf files? > > The method of setting that during initdb changed in 9.2: > > http://www.postgresql.org/docs/9.4/interactive/release-9-2.html > > E.29.3.1.7.1. postgresql.conf > > Identify the server time zone during initdb, and set postgresql.conf > entries timezone and log_timezone accordingly (Tom Lane) > > This avoids expensive time zone probes during server start. > >> >> >> Cheers, >> -- >> Willy-Bas Loos >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Willy-Bas Loos
