Thread: pattern matching with dates?
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. thanks much, Tom -- Thomas Good, Senior Database Administrator Saint Vincent's Hospital Westchester A Division of Saint Joseph's Medical Center Residential Services 75 Vanderbilt Avenue, Room 5-47 Staten Island, NY 10304 718.818.5528
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) = 1where 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 ywhere ('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)
Hello Thomas, On 05.01.2011 20:39, Good, Thomas wrote: > select * from db_log where log_date LIKE '2011-01-%'; The lazy way would be something like this: SELECT * from tab WHERE log_date::VARCHAR LIKE '2011-01-%'; The more proper way is: SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and EXTRACT(MONTH FROM log_date) = 1; Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com
Sorry, but this examples not use index. On 1/7/11, Susanne Ebrecht <susanne@2ndquadrant.com> wrote: > Hello Thomas, > > On 05.01.2011 20:39, Good, Thomas wrote: >> select * from db_log where log_date LIKE '2011-01-%'; > > The lazy way would be something like this: > SELECT * from tab WHERE log_date::VARCHAR LIKE '2011-01-%'; > > The more proper way is: > SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and > EXTRACT(MONTH FROM log_date) = 1; > > Susanne > > -- > Susanne Ebrecht - 2ndQuadrant > PostgreSQL Development, 24x7 Support, Training and Services > www.2ndQuadrant.com > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent from my mobile device ------------ pasman
On 07/01/11 14:15, pasman pasmański wrote:
> Sorry, but this examples not use index.
Looks like it does here (oh, and please try to post your replies at the
bottom of the message)
> On 1/7/11, Susanne Ebrecht<susanne@2ndquadrant.com> wrote:
>> The more proper way is:
>> SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and
>> EXTRACT(MONTH FROM log_date) = 1;
CREATE TABLE d_table (d date);
INSERT INTO d_table SELECT '2001-01-01'::date + generate_series(1,9999);
CREATE INDEX d_extract_idx ON d_table ( extract('day' FROM d) );
EXPLAIN ANALYSE SELECT * FROM d_table WHERE extract('day' FROM d) = 3;
That ends up doing a bitmap index scan for me.
Of course, it's entirely possible an index on year+month returns too
many rows to be useful.
-- Richard Huxton Archonet Ltd
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