Re: [8.1.4] Create index on timestamp fails

On Wed, Aug 23, 2006 at 09:42:00AM -0400, Tom Lane wrote:

> It sounds a bit bogus to me too.  Another possibility is to keep the
> data storage as timestamptz (which is really the recommended type for
> any sort of real time values), and define the index on
>
>     date_part('day', entry_time AT TIME ZONE 'GMT')
That definitely sounds reasonable.

> (or whatever zone you choose to use for reference).  However, to use the
> index you'd have to spell the queries exactly like that, so the PITA
> factor might be too high.
An SQL function "gmt_tz(timestamptz)" might help to cut down
on the fuss:

 select ... from tbl where gmt_tz(tbl.a_tz) between ...;

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