Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 0B6102E0091 for ; Thu, 24 Apr 2008 12:45:06 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 90103-02 for ; Thu, 24 Apr 2008 12:44:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.28]) by developer.postgresql.org (Postfix) with ESMTP id 19A472E0083 for ; Thu, 24 Apr 2008 12:44:52 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 5so182390ywb.73 for ; Thu, 24 Apr 2008 08:44:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=iouUfujrqNxD9RGMqLUsCS6fX7V4lfwPJc79KTSPA9s=; b=fRkrYxMriGYGiufwjcfbCHFFNblycx8BJp8/NHHzcQT1FkwRaiv/Ve2wL33PRZOiIKT1ICMm5CNWRX3z4a2A4EX5Abq9OI5PGJInBKSeJqVg2MBSA+UC46W+Bo2TLkuqpAkCYQlWJIPYZqtEBAZaZl2tTbElBPZ14cXQmjQG8DM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=SM4fRktyQXGvOK+Pcu7lIUgODgXjxd8V/Nh69vhq6sIM3dcInWS9bPPq/nzCfsJvAcNTWESnozp5eFclTJ/uvvx9/j7sPNgvtBjpqQQHaRbISto9snwnneCQMPgCJsRteT3BBJCPclnQ0s/UgQAPLWkkrBcWnVOGIE2c2WzfLBA= Received: by 10.114.159.1 with SMTP id h1mr1963997wae.147.1209051885001; Thu, 24 Apr 2008 08:44:45 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Thu, 24 Apr 2008 08:44:44 -0700 (PDT) Message-ID: Date: Thu, 24 Apr 2008 09:44:44 -0600 From: "Scott Marlowe" To: "Fernando Hevia" Subject: Re: First day of month, last day of month Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <481085D1.2010807@sympatico.ca> <48108813.6070401@sympatico.ca> <481095FB.7090600@denterprises.org> <023b01c8a61d$5855cea0$8f01010a@iptel.com.ar> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/170 X-Sequence-Number: 30709 On Thu, Apr 24, 2008 at 9:16 AM, Scott Marlowe wrote: > On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia wrote: > > > De: pgsql-sql-owner@postgresql.org > > > [mailto:pgsql-sql-owner@postgresql.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;