Working with Timezones   

Using to_char

--We're in Boston
SELECT to_char(CURRENT_TIMESTAMP, 'Day Mon dd, yyyy HH12:MI AM (TZ)');
-- outputs--
Sunday    Jul 08, 2012 11:07 AM (EDT)

set TimeZone='US/Central';
SELECT to_char(CURRENT_TIMESTAMP, 'Day Mon dd, yyyy HH12:MI AM (TZ)');

--output is --
Sunday    Jul 08, 2012 10:07 AM (CDT)


--if you convert note how the TZ is no longer present --
-- because its just a timestamp
SELECT to_char(CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles', 'Day Mon dd, yyyy HH12:MI AM (TZ)');

--output is --
Sunday    Jul 08, 2012 08:07 AM ()

The function hack

We see we can change the time zone and have to_char register a date time for that time zone. One way which I admit is not that elegant is to wrap the set call in a function and then reset when done. It's not elegant mostly because it's not clear if there would be side issues resulting from this. In a parallel process there might be, but since PostgreSQL doesn't support parallel processing at this time, there shouldn't be.

--Original function --
-- The function --
CREATE OR REPLACE FUNCTION date_display_tz(param_dt timestamp with time zone, param_tz text, param_format text)
  RETURNS text AS
$$
DECLARE var_prev_tz text; var_result varchar;
BEGIN
-- store prior timezone so we can reset it back when we are done
SELECT setting INTO var_prev_tz FROM pg_settings WHERE name = 'TimeZone'; 
EXECUTE 'set timezone = ' || quote_literal(param_tz)  ;
var_result := to_char(param_dt, param_format);
--reset time zone back to what it was
EXECUTE 'set timezone = ' || quote_literal(var_prev_tz) ;
return var_result;
END;$$
  LANGUAGE plpgsql VOLATILE;
  
-- Simon Bertrang's version 
-- uses local to obviate the need to reset the timezone
-- further refined with Bruce Momjian's suggestion to use set_config
-- instead of dynamic sql
CREATE OR REPLACE FUNCTION date_display_tz(param_dt timestamp with time zone, param_tz text, param_format text) 
 RETURNS text AS 
$$
DECLARE var_result varchar;
BEGIN
EXECUTE 'set local timezone = ' || quote_literal(param_tz);
PERFORM set_config('timezone', param_tz, true);
var_result := to_char(param_dt, param_format);
RETURN var_result;
END;
$$ language plpgsql VOLATILE; 


COMMENT ON FUNCTION date_display_tz(timestamp with time zone, text, text) IS 'input param_dt: time stamp with time zone
param_tz: desired output time zone
param_format: desired date output

This function will return the timestamp formatted using the time zone local time';

Now to take our new function for a test drive

-- we use the FM in front to get rid of the 9 char padding
SELECT tz_name, date_display_tz(CURRENT_TIMESTAMP, tz_name, 'FMDay Mon dd, yyyy HH12:MI AM (TZ)')
FROM (VALUES ('US/Eastern')
    , ('US/Central')
    , ('America/Los_Angeles') ) AS t(tz_name);

      tz_name       |          date_display_tz
--------------------+------------------------------------
US/Eastern          | Sunday Jul 08, 2012 08:05 PM (EDT)
US/Central          | Sunday Jul 08, 2012 07:05 PM (CDT)
America/Los_Angeles | Sunday Jul 08, 2012 05:05 PM (PDT)