Re: [8.1.4] Create index on timestamp fails

Arturo Perez wrote:
>
> On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:

> >I seriously doubt that. date_part on a timestamptz is stable, not
> >immutable, and AFAICT has been marked that way since 7.3. The problem
> >is that the results depend on your current TimeZone setting --- for
> >instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.
> >
> >If you only need day precision, try storing entry_date as a date
> >instead
> >of a timestamptz. Or perhaps consider timestamp without tz. But you
> >need something that's not timezone-dependent to make this work.
>
> Ah, I knew it was something I was overlooking. Thanks a ton. We need
> sub-day granularity (it's for a sort of weblog). Without a TZ sounds
> llke a winner.

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.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.