On Wed, Jan 5, 2011 at 11:39 AM, 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?
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.
I'd think that avoiding treating the date as a string would lend itself to using indexes so would get much faster results, but even without indexes, handling dates as binary types is likely faster than regex comparisons on the column cast to a string.
I made your original query work on my 8.4.x database by just explicitly casting the column to text like this:
select * from db_log where log_date::text LIKE '2011-01-%'
Are you looking for all rows where log_date is in january, 2011 or where log_date is in current month or something else? I can think of lots of potential ways to solve this:
where log_date >= date_trunc('month', '2011-01-01'::date)
where date_trunc('month', log_date) = '2011-01-01'
where log_date >= date_trunc('month', current_date)
where date_trunc('month', log_date) = date_trunc('month', current_date)
where date_part('year', log_date) = 2011 and date_part('month', log_date) = 1
where date_part('year', log_date) = date_part('year', current_date) and date_part('month', log_date) = date_part('month', current_date)
where log_date between x and y
where ('2011-01-01'::date, '2011-01-01'::date + interval '1 month') overlaps (log_date, log_date)
where (current_date, current_date + interval '1 month') overlaps (log_date, log_date)