Re: 'NOW' in UTC with no timezone

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Stuart Bishop <stuart(at)stuartbishop(dot)net> writes:
>>> I'm trying to determine the best way of saying 'The current time in UTC
>>> with no time zone information'.
>>
>> Isn't that a contradiction in terms?

> Not if you're used to the Unix concept of storing "seconds since the epoch".
> In that model the quantity you're storing is entirely time zone agnostic.

Not at all. In my worldview, the Unix concept is "seconds since
midnight 1/1/1970 00:00 UTC", and therefore it is essentially UTC time,
because (a) its absolute meaning doesn't change depending on your local
timezone, but (b) unless you are in UTC, you have to rotate it to your
local timezone for display.

For comparison, various not-Unix operating systems get this wrong, and
store seconds since local-time midnight, simplifying display at the
price of not knowing what time it Really Is.

> The SQL approach of storing a time zone with the timestamp makes things very
> confusing. For unix people it requires a time zone in precisely the opposite
> circumstances from when they expect to use one.

Yes, obviously you are confused ;-)

Postgres implements TIMESTAMP WITH TIME ZONE as the Unix concept: what
is stored internally is seconds since the UTC epoch. We rotate to or
from local timezone for input/display. TIMESTAMP WITHOUT TIME ZONE is
essentially the other idea: it stores seconds since a local-midnight
epoch in an unspecified time zone. No timezone adjustment is done
during input or display.

If timezones are at all significant in terms of your application, you
almost certainly want to be storing your data as TIMESTAMP WITH TIME ZONE,
which amounts to asserting that you know what time the values Really Are
in global terms. Otherwise the rotation facilities are going to be
fighting you every step of the way.

(Note that this is arguably not what the SQL standard means by TIMESTAMP
WITH TIME ZONE, but it's what Postgres implements.)

> It could be useful to represent "3pm in your local time zone" which can be
> useful for some purposes.

TIME WITHOUT TIME ZONE?

regards, tom lane