Обсуждение: Generating a list of days

Hi,

I'm trying to generate a list of days between a start
date and an end date for use in a query - I would then
do an outer join with some other data for graphing
purposes as I need all the days with 0 entries.

I using perl as the front-end, but I'd rather do it in
sql if poss.

Any ideas?

TIA

Fraser

__________________________________________________
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/

> I'm trying to generate a list of days between a start
> date and an end date for use in a query - I would then
> do an outer join with some other data for graphing
> purposes as I need all the days with 0 entries.

istm that you would be able to simply qualify your query with a

WHERE your_date_column BETWEEN 'your_start_date" AND 'your_end_date'

You can use GROUP BY/HAVING to get missing results, or just select on
the NULL columns in the outer join you have already suggested.

If you put the date column in the output also then you would have the
list available at that point.

If you are using perl and actually want a complete list of the *missing*
dates, which is of course what you said you wanted, I would suggest
doing the above query (which can be efficient) and then generate the
list of missing dates using perl (which is also efficient generating a
list).

Folks may have other good suggestions too...

hth

                      - Thomas

I know you said you'd prefer SQL, but perl can do this very easily using
Time::Piece or one of the other date modules that support date arithmetic, in a
very natural way. Sure you want to use SQL for this?

Wes Sheldahl



Fraser Murray <frasernm%yahoo.co.uk@interlock.lexmark.com> on 03/15/2002
03:51:11 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Generating a list of days


Hi,

I'm trying to generate a list of days between a start
date and an end date for use in a query - I would then
do an outer join with some other data for graphing
purposes as I need all the days with 0 entries.

I using perl as the front-end, but I'd rather do it in
sql if poss.

Any ideas?

TIA

Fraser

__________________________________________________
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

here is a query I use that will get data grouped by the last seven days.
Now, beware, if there is no data for any day then no record for that day
will be returned, so you would have to fill the gaps in your code.

select date_trunc('day', event_time) as event_day, count(*)
from event
where event_time between (date_trunc('day', current_timestamp) + '-7
days') AND date_trunc('day', current_timestamp)
group by event_day

There are other goofy things you could do in SQL:

select last_7days.my_day
from (
        select date_trunc('day',  current_timestamp) as my_day
        union
        select date_trunc('day', current_timestamp) + '-1 days' as my_day
        union
        select date_trunc('day', current_timestamp) + '-2 days' as  my_day
        union
        select date_trunc('day', current_timestamp) + '-3 days' as  my_day
        union
        select date_trunc('day', current_timestamp) + '-4 days' as my_day
        union
        select date_trunc('day', current_timestamp) + '-5 days' as my_day
        union
        select date_trunc('day', current_timestamp) + '-6 days' as my_day
) as last_7days

;-)



> > I'm trying to generate a list of days between a start
> > date and an end date for use in a query - I would then
> > do an outer join with some other data for graphing
> > purposes as I need all the days with 0 entries.
>
> istm that you would be able to simply qualify your query with a
>
> WHERE your_date_column BETWEEN 'your_start_date" AND 'your_end_date'
>
> You can use GROUP BY/HAVING to get missing results, or just select on
> the NULL columns in the outer join you have already suggested.
>
> If you put the date column in the output also then you would have the
> list available at that point.
>
> If you are using perl and actually want a complete list of the *missing*
> dates, which is of course what you said you wanted, I would suggest
> doing the above query (which can be efficient) and then generate the
> list of missing dates using perl (which is also efficient generating a
> list).
>
> Folks may have other good suggestions too...
>
> hth
>
>                       - Thomas
>
> ------------------------------
>