| 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: | Whole Thread | Raw Message | 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 |