Re: [8.1.4] Create index on timestamp fails

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
Cc: pgsql-general(at)postgresql(dot)org, Arturo Perez <aperez(at)hayesinc(dot)com>
Subject: Re: [8.1.4] Create index on timestamp fails
Date: 2006-08-23 13:42:00
Message-ID: 18556.1156340520@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2006-08-23 13:55:27 Re: [8.1.4] Create index on timestamp fails
Previous Message Bryan White 2006-08-23 13:38:28 Re: share library version problems