From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: pattern matching with dates? |
Date: | 2011-01-08 10:22:23 |
Message-ID: | ig9dsv$8v3$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2011-01-05, Good, Thomas <tgood(at)svcmcny(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | gvim | 2011-01-11 02:14:12 | Find NOT NULLs in a group of 20 columns |
Previous Message | Russell Galyon | 2011-01-08 04:48:17 | Re: return records with more than one occurrences |