Re: 'NOW' in UTC with no timezone

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.

> But anyone who is worried about timezones and yet is storing his data in
> timestamp-without-time-zone columns probably needs to reconsider exactly
> what his data represents.

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. And It means two timestamps
representing the same point in time can have subtly different behaviours if
they're stored with different time zones.

I think what this user wants is to store a "timestamp with time zone" and
always store his time with the time zone "UTC". That lets him store timestamps
using the time since epoch mentality, but print them accurately in whatever
time zone he wants.

If you stored them "without time zone" then postgres wouldn't let you easily
display them in non-UTC time zones. It considers them to be a particular time
of a particular day in whatever time zone you're in.

It could be useful to represent "3pm in your local time zone" which can be
useful for some purposes. For example, I'm using it to represent the expiry
time of specials, since they expire on a particular date in your local time
zone. If you transport the printout from one time zone to another the expiry
time actually changes. In practice I would have been just as happy storing UTC
and then printing using "AT TIMEZONE UTC".

--
greg