Re: First day of month, last day of month

On Thu, Apr 24, 2008 at 9:16 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia <fhevia(at)ip-tel(dot)com(dot)ar> wrote:
> > > De: pgsql-sql-owner(at)postgresql(dot)org
> > > [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Scott Marlowe
> >
> > >
> > > Then you can just use date_trunc on the values in the
> > > database. Plus if you're using timestamp WITHOUT timezone,
> > > you can index on it.
> > >
> >
> > Did not understand this. Are you saying timestamps WITH timezone are NOT
> > indexable or you mean that you cant build a partial index on a
> > timestamp-with-time-zone returning function?
>
> Correct, timestamptz or timestamp with timezone (timestamptz is the
> shorter alias) are not indexable because functions in an index must be
> immutable, and date_trunc on a timestamptz is not.

Note that if you are storing your time stamp as timestamptz, you can
use the "at time zone 'xyz'" construct to create an index, and as long
as you retrieve them with the same construct you'll get to use the
index.

create index test_ts_month_trunc on testtable (date_trunc('month',(ts
at time zone 'MST')));
select * from testtable where date_trunc('month',(ts at time zone
'MST'))='2007-10-01 00:00:00'::timestamp;