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

On 2010-12-02, Basil Bourque <basil(dot)list(at)me(dot)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