Re: [8.1.4] Create index on timestamp fails

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> On Wed, Aug 23, 2006 at 09:07:35AM -0400, Alvaro Herrera wrote:
>> Another idea would be to separate the date column (which would have the
>> index) from the time column (which would have the timezone). The
>> timezone is important -- if you have "bloggers" from all around the
>> world you're gonna have serious problems with the archived time.

> Would that indeed work ? I mean, depending on the time zone
> the *date* might be different by +/-1, too ?

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

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

regards, tom lane