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

On Mon, Feb 19, 2007 at 12:03:07PM +0100, Karsten Hilbert wrote:
> > 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 ?

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

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.

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

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

Right.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.