Re: timezone() with timeofday() converts the wrong direction? - Mailing list pgsql-general

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/