On Thu, Apr 21, 2005 at 05:56:41PM -0700, Steve - DND wrote: > > Okay, I understand what you're saying now, but then is a time without a > timezone implicitly assumed to be UTC? Is there a way to explicitly make the > timezone on the stamp be UTC, if the prior is not the case? See "Date/Time Types" in the "Data Types" chapter of the documentation: http://www.postgresql.org/docs/8.0/interactive/datatype-datetime.html "Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different zone reference can be specified for the conversion using AT TIME ZONE." "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client." As far as I know, it's not possible to get output like the following from the same query if the data type is timestamp with time zone: 2005-04-21 15:00:00-07 2005-04-21 22:00:00+00 However, I suppose you could muck around with the TimeZone configuration variable and cast the timestamps to text: CREATE FUNCTION tzconvert(timestamp with time zone, text) RETURNS text AS ' DECLARE ts ALIAS FOR $1; tz ALIAS FOR $2; tmptz text; retval text; BEGIN tmptz := current_setting(''TimeZone''); PERFORM set_config(''TimeZone'', tz, TRUE); retval := ts; PERFORM set_config(''TimeZone'', tmptz, TRUE); RETURN retval; END; ' LANGUAGE plpgsql VOLATILE STRICT; SELECT tzconvert('2005-04-21 15:00:00-07', 'PST8PDT') AS pacific, tzconvert('2005-04-21 15:00:00-07', 'UTC') AS utc; pacific | utc ------------------------+------------------------ 2005-04-21 15:00:00-07 | 2005-04-21 22:00:00+00 (1 row) Maybe somebody knows of an easier way to do that. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
