From: | Basil Bourque <basil(dot)list(at)me(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Get server's time in UTC time zone, in ISO 8601 format |
Date: | 2010-12-02 03:03:23 |
Message-ID: | F9808274-0140-4E03-A0FF-3C853E1346A6@me.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Googling for 3 hours has taught me much about Postgres' handling of time, but has not answered the question:
--> How to get the server's time in UTC time zone ('ZULU'), in ISO 8601 format, by executing a simple SELECT statement?
This 2-step approach works:
set time zone 'UTC';
select current_timestamp;
That does render the actual UTC time (8 hours ahead of US west coast time):
2010-12-02 00:24:56.284816+00
Note the timezone on the end: +00 (that's a good thing)
Surely there must be a way to do this in a single SELECT.
I tried using "AT TIME ZONE":
select current_timestamp AT TIME ZONE 'ZULU'
That does indeed give me the UTC time, but without a timezone on the end such as +00 or z or zulu:
2010-12-02 00:29:05.735597
Note the lack of +00 on end (that's a bad thing)
I'm using Postgres 9 with Mac OS X, using United States UTF-8 settings.
--Basil Bourque
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-12-02 03:18:44 | Re: Get server's time in UTC time zone, in ISO 8601 format |
Previous Message | Tony Day | 2010-12-02 01:05:50 | Differing performance between Ubuntu 10.04 and 10.10 |