Re: Why *exactly* is date_trunc() not immutable ?

On Mon, Feb 19, 2007 at 10:58:50AM +0100, Martijn van Oosterhout wrote:

> > I'll solve it with a date_trunc_utc() wrapper.
>
> It should be noted the date_truc(timestamptz) is not immutable, whereas
> date_trunc(timestamp) is. Thus you should be able to make an index on:
>
> date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )
Ah, that makes it clear *why* this should work.

I would assume to get meaningful results from a query using
that index I'd have to normalize input timestamps to UTC,
too, before putting them into the query, right ?

> OTOH, if you're only storing times in UTC, then timestamp without
> timezone might be better anyway.
Well, PostgreSQL itself is storing UTC anyways but we need
the timezone bit since our frontend delivers timestamps from
various timezones and they are note normalized to UTC before
they get to the database.

IOW, I want the database to force programmers to have to
think about from which timezone they deliver timestamps into
a date-of-birth field into.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346