PostgreSQL: Construct a Query That Takes a Date Range

Question

I have a function that takes a date range as input and returns a dictionary. I'm using a for-loop. I'd like to do whole thing in PostgreSQL. What's the best way?

function body:

ids = {}

for current_date in rrule.rrule(rrule.DAILY, dtstart=start_date,
                                until=end_date):
    id = sql.with_query_result("""
            SELECT id
            FROM record
            WHERE type='default'

            UNION

            SELECT id
            FROM record
            WHERE extract(isodow from (:current_date)::timestamp) IN day_ids

            UNION

            SELECT id
            FROM record
            WHERE utc_dates @> (:current_date)::timestamp

            ORDER BY priority DESC
            LIMIT 1
            """, {'current_date': current_date})
    ids[current_date] = id

schema:

id integer
type text
day_ids integer[]
utc_dates tsrange
priority integer

Clodoaldo Neto · Accepted Answer · 2013-10-12 12:19:04Z

1

This is my try without data to test and without knowing about that sql.with_query_result driver method:

rs = sql.with_query_result("""
    select distinct on (_timestamp::date)
        _timestamp::date, id
    from
        record
        inner  join
        unnest(:current_date::timestamp[]) d(_timestamp) on
            utc_dates @> _timestamp
            or
            extract(isodow from _timestamp) = any(day_ids)
            or
            "type" = 'default'
    order by 1, priority desc
    """,
    {
        'current_date':
        rrule.rrule(rrule.DAILY, dtstart=start_date, until=end_date)
    }
)

ids = {}

for (current_date, id) in rs:
    ids[current_date] = id

answered Oct 12, 2013 at 12:19

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FrViPofm · Accepted Answer · 2013-11-23 00:21:41Z

You can implement the python dateutil rrule in postgresql.

CREATE OR REPLACE FUNCTION _pc_after(
    t_rule TEXT,
    t_after TIMESTAMP,
    inc BOOLEAN DEFAULT TRUE)
    RETURNS TIMESTAMP AS
$$
    from dateutil.rrule import *
    from dateutil.parser import *

    rule = rrulestr(t_rule)
    _after = parse(t_after)
    occurence = rule.before(_after, inc=False)
    return occurence
$$
LANGUAGE 'plpythonu' VOLATILE;

-- = simple before function =
CREATE OR REPLACE FUNCTION _pc_before(
    t_rule TEXT, 
    t_before TIMESTAMP,
    inc BOOLEAN DEFAULT TRUE) 
    RETURNS TIMESTAMP AS
$$
    from dateutil.rrule import *
    from dateutil.parser import *

    _rule = rrulestr(t_rule)
    _before = parse(t_before)
    occurence = _rule.before(_before, inc=False)
    return occurence
$$
LANGUAGE 'plpythonu' VOLATILE;

-- = simple between function =
CREATE OR REPLACE FUNCTION _pc_between(
    t_rule TEXT, 
    t_after TIMESTAMP, 
    t_before TIMESTAMP,
    inc BOOLEAN DEFAULT FALSE) 
    RETURNS SETOF TIMESTAMP AS
$$
    from dateutil.rrule import *
    from dateutil.parser import *

    _rule = rrulestr(t_rule)
    _after = parse(t_after)
    _before = parse(t_before)
    occurences = _rule.between(_after, _before, inc)

    return occurences
$$
LANGUAGE 'plpythonu' VOLATILE;

CREATE OR REPLACE FUNCTION _pc_between(
    t_rule TEXT, 
    t_after TIMESTAMP, 
    t_before TIMESTAMP,
    inc BOOLEAN DEFAULT FALSE) 
    RETURNS SETOF TIMESTAMP AS
$$
    from dateutil.rrule import *
    from dateutil.parser import *

    _rule = rrulestr(t_rule)
    _after = parse(t_after)
    _before = parse(t_before)
    occurences = _rule.between(_after, _before, inc)

    return occurences
$$
LANGUAGE 'plpythonu' VOLATILE;

So :

SELECT _pc_between('DTSTART:20130102T090000
    RRULE:FREQ=DAILY;INTERVAL=10;COUNT=25',
    cast('2013-01-01' as timestamp),
    cast('2013-05-31' as timestamp)
);

will return :

"2013-01-02 09:00:00"
"2013-01-12 09:00:00"
"2013-01-22 09:00:00"
"2013-02-01 09:00:00"
"2013-02-11 09:00:00"
"2013-02-21 09:00:00"
"2013-03-03 09:00:00"
"2013-03-13 09:00:00"
"2013-03-23 09:00:00"
"2013-04-02 09:00:00"
"2013-04-12 09:00:00"
"2013-04-22 09:00:00"
"2013-05-02 09:00:00"
"2013-05-12 09:00:00"
"2013-05-22 09:00:00"

Collectives™ on Stack Overflow

PostgreSQL: Construct a Query That Takes a Date Range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related