Scott Mohekey <scott.mohekey@telogis.com> wrote:
> I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as
> TIMESTAMP at GMT. We then convert it to a users local timezone
> within application code.
That sounds like an accident waiting to happen. Sure, you can make
it work, but you're doing things the hard way, and the defaults will
probably be to do the wrong thing.
TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, in that
it doesn't store a time zone with the timestamp. What it does do is
store the timestamp in GMT, so that it represents a moment in time,
changing the representation of the moment to local time in any time
zone as needed. This sounds a lot like what you're trying to do --
a natural fit. If you want to see it in GMT, that easy enough. If
you want to see it as local time in any other time zone, that's
easily done without risk of actually getting a timestamp
representing the wrong moment.
TIMESTAMP WITHOUT TIME ZONE is stored "raw" and is not considered to
be associated to a time zone until you do so. It will default to
assigning the time zone set on your server, which is normally your
local time zone. Unless that's GMT, you will need to be very
careful to always localize the timestamp to GMT before doing
anything with it.
-Kevin