Re: 'NOW' in UTC with no timezone

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

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

Well one sense it has no time zone since it's just a quantity of time. The
number of seconds since the epoch to a particular point in time is the same no
matter where you are. In another sense it's related to UTC because the epoch
is specified in UTC. That's why the user's description of "The current time in
UTC with no time zone information" is applicable.

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

Hm. Further experimentation shows I was indeed confused.

I guess my confusion comes from the way postgres interprets unadorned time
stamps as being in local time. And then always displays timestamps converted
to local time. I thought it was remembering the time zone specified in the
original input. In fact it's not doing that.

I am beginning to like the idea you suggested of leaving the server set to UTC
and just manually specifying time zones whenever I want to convert to local
time.

--
greg