Re: pattern matching with dates?

On Wed, Jan 5, 2011 at 11:39 AM, 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?
>
> 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)

http://www.postgresql.org/docs/8.4/static/functions-datetime.html