How can I set the database time zone for a query?

I am working on two systems which access the same PostgreSQL database:

• a Windows exe app, and
• a Code Igniter PHP web app

Both systems output basically the same stuff.

One issue I’m having currently is that I need to produce a report of data which is specific to a “location” which is in a different time zone to the server. All date/time data for readings, etc are stored in TIMESTAMPTZ format.

Both systems first set the database time zone calling a function which does…

EXECUTE 'SET TIME ZONE ''' || (
        SELECT time_zone_name
            FROM  location
            WHERE location_code = in_location_code) || '''';

They then call the same stored procedure to construct the XML to pass through the same XSL transform to end up as an HTML/PDF report.

The Windows exe creates the report with the correct time-adjusted “Report Generated at…” date in the heading and all relevant dates/times in the report are correctly offset by the time zone difference.

But the PHP app’s not correctly setting the heading date/time, and all the internal dates throughout the report show the server time zone time, not the offset for the remote location.

I can see the start and end date getting passed from the PHP app to the d/b query with the dates showing the correct date/time including the TZ offset but for all the world, it seems the d/b is ignoring the previous SET TIME ZONE call.

Answer

Found the issue myself in the end.

The PHP app was setting the time zone correctly, but then there were multiple subsequent calls setting the search_path and I think it was this which reset the time zone back to server time.

In the end, I added a line into the various report stored procedures to set the time zone in there, making sure any TIMESTAMPTZ variable assignments were made after this.

It didn’t affect the PC app but fixed the PHP app.