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

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

Basil Bourque <basil.list@me.com> writes:
> -->  How to get the server's time in UTC time zone ('ZULU'), in ISO 8601 format, by executing a simple SELECT
statement?

You already found it:

>   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)

Why do you need that?  You told it what time zone you wanted.

            regards, tom lane

On 12/01/10 22:03, Basil Bourque wrote:
>    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)



select (current_timestamp at time zone 'ZULU')||'+00';

On 2010-12-02, Basil Bourque <basil.list@me.com> wrote:
> 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)

this is the text representation of values of type timestamp with
timezone (AKA timestamptz), if you change the datestyle setting you
may get a different representation of tthe time.

> 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

that query gives a value of type timestamp.  when converted to text it
has no offset part.

timestamp is a different type to timestamptz, with different properties.

by the time it's converted now() from timestamptz to timestamp postgres has
forgotten what zone you asked for.

if you need to calculate the offset compare the timestamp as the
requested zone with that at the UTC zone.

If you are interested in exactly formatted  string representations of
timestamps use the to_char function.


If you are looking for a way to store a timestamp and a timezone
postgres has no such type.

timestamp has a time but no zone information to give it context.

timestamptz values are converted on input and stored internally in UTC

--
⚂⚃ 100% natural