Skip site navigation (1) Skip section navigation (2)

Re: expression index on date_trunc

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: <a(dot)redhead(at)openinternetsolutions(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: expression index on date_trunc
Date: 2008-01-30 22:36:16
Message-ID: 877ihruejj.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-general
<a(dot)redhead(at)openinternetsolutions(dot)com> writes:

>   CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day', request_received));
...
> I'd be grateful if someone could point out what part of the statement is not
> IMMUTABLE or how I could mark my create index statement as being immutable.

date_trunc(timestamp with time zone) is not immutable because it depends what
your current time zone is. That is, if you change what time zone you're in a
timestamp with time zone could appear to change from one day to another.

However date_trunc(timestamp without time zone) is immutable. So I think what
you have to do is build your index on:

date_trunc('day', request_received AT TINE ZONE 'GMT')

or whatever time zone you're interested in. That will get you the day part of
that timestamp at that time zone (because it first casts it to a timestamp
without time zone for the time zone you specify).

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

In response to

pgsql-general by date

Next:From: Vyacheslav KalininDate: 2008-01-30 22:40:16
Subject: Re: aggregate hash function
Previous:From: Daniel VeriteDate: 2008-01-30 22:32:47
Subject: Re: expression index on date_trunc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group