FAQ
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

Search Discussions

  • Samuel Gendler at Jan 7, 2011 at 10:44 am

    On Wed, Jan 5, 2011 at 11:39 AM, Good, Thomas 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
  • Susanne Ebrecht at Jan 7, 2011 at 12:40 pm
    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
  • Pasman Pasmański at Jan 7, 2011 at 2:15 pm
    Sorry, but this examples not use index.
    On 1/7/11, Susanne Ebrecht 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
  • Richard Huxton at Jan 7, 2011 at 4:09 pm

    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 Ebrechtwrote:
    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
  • Jasen Betts at Jan 8, 2011 at 10:22 am

    On 2011-01-05, Good, Thomas 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-sql @
categoriespostgresql
postedJan 5, '11 at 8:26p
activeJan 8, '11 at 10:22a
posts6
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase