To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: pattern matching with dates?
Date: 8 Jan 2011 10:22:23 GMT
Organization: Dis (not Dat) Organisation
Lines: 42
Message-ID: <ig9dsv$8v3$1@reversiblemaps.ath.cx>
References: 
 <FA041D403EDFCD4397E1F6382360ECFCFDB5697143@SVCMCEMAIL.svcmc.svcmcny.org>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2011-01-05, Good, Thomas <tgood@svcmcny.org> wrote:
> This dubious query worked well previously:
> select * from db_log where log_date LIKE '2011-01-%';
> (currently works on bluehost.com where they run 8.1.22)
>
> Can someone offer a (preferably ANSI compliant) way to do this on 8.4.5?

  where date_trunc( log_date,'month') = '2011-01-01'::timestamp;

or standard

  where extract( 'year' from log_date) = 2011 and extract ( 'month' from log_date) = 1;


this:

  where cast( log_date as varchar ) like '2011-01-%'

is as far as I can tell standard, but is almost certainly non-portable 
as it is dependant of the character format used for casting dates to
varchar.


> I realize that >= and so on work well (which may explain why the docs
> are pretty silent about pattern matching with dates) but sometimes it's nice to 
> treat the (ISO) date as a string.

">= etc" will outperform date_trunc, like , and extract if the date column
is indexed. the performance of % can be improved in recent versions by
indexing on the expression (log_date::text)


best performance is probably

 where log_date between '2011-01-01'::date and  '2011-01-01'::date + '1 month - 1 day' ::interval;

or standard (I think)

 where log_date between cast('2011-01-01' as date) and  cast ( '2011-01-01' as date) + cast ( '1 month - 1 day' as interval) ;

-- 
⚂⚃ 100% natural