Get server's time in UTC time zone, in ISO 8601 format

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

Responses

Browse pgsql-novice by date

  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