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

On Mon, Feb 19, 2007 at 12:53:15PM +0100, Martijn van Oosterhout wrote:

> Well, your queries need to use the same form, ie:
>
> SELECT blah FROM foo
> WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' )
Thought so.

> That seems a bit error prone though, so your idea of making a simple
> SQL function to wrap it will probably save you much heartache. It will
> also make it clearer to people reading the code *why* it is written
> that way.
Yep, and the COMMENT ON FUCNTION provides for a nice place to document it :-)

> > 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.
>
> Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the
> index is probably easier than everywhere else. Just checking you'd
> thought about it. :)
Thanks !

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