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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
