Re: Get timestamp as UTC - Mailing list pgsql-general

On Apr 22, 2005, at 2:51, Steve - DND wrote:

> I was under the impression that using timezone('UTC',
> now())::timestamptz
> would give me the current UTC time, with timezone offset. Instead I am
> getting the UTC time, but with an offset of -07(my local time). How do
> I get
> UTC time, with the 0 offset that it should be?
>

If you want the server to return time zone information respective of
another time zone, I believe you'll need to use SET TIME ZONE.
Otherwise, the server automatically (converts the timestamp) and
returns the time zone of the server.

For example,

test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 16:26:57.209082+09
(1 row)

test=# set time zone 'UTC';
SET
test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 07:27:55.841596+00
(1 row)

test=# select (current_timestamp at time zone 'UTC');
           timezone
----------------------------
  2005-04-22 07:28:48.888154
(1 row)

test=# select (current_timestamp at time zone 'UTC')::timestamptz;
            timezone
-------------------------------
  2005-04-22 07:38:19.979511+00
(1 row)

I'm using at alternative form of the timezone() function. It should
work the same. I'm also using current_timestamp which follows the SQL
spec, now() is PostgreSQL-specific, though it works the same.

As you'll notice, timezone() applied to a timestamptz returns a
timestamp; applied to a timestamp, timezone() returns timestamptz.

In the first example, I've applied the function twice to return
timestamptz. The server returns it in the server time zone (in my case,
JST). I then set the server time zone to UTC. (I believe this is only
for my session. Other sessions are unaffected.) In the second example,
you can see it returns the timestamp at UTC. In the third example, I've
only applied AT TIME ZONE once, so it returns a timestamp, and you can
see that it's relative to UTC.

In you example, as there is no specified timezone  (as the timezone()
call returns a timestamp without time zone), it applies the time zone
of the server.

In the example I've given, timezone the outermost timezone() is
returning timestamptz at UTC, so the ::timestamptz cast is not needed.
In your case, the timestamptz basically does the same thing: it returns
a timestamptz at the server time zone.

Internally, timestamptz is represented the same regardless of time
zone. The returned representation of that timestamptz is dependent on
the server settings. To avoid this kind of hassle, I usually return the
Unix epoch using EXTRACT(epoch from current_timestamp) and format the
timestamptz in my application.

Hope this helps.

Michael Glaesemann
grzm myrealbox com