On 2011-01-05, Good, Thomas <tg...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql