Generate sequence of dates used in for loop

Question

I have a series of tables partitioned by dates and named in the following format:

public.schedule_20121018

Is there a way to generate a sequence of dates in the above 20121018 pattern so that I can do a for loop SELECT through information_schema.tables?

For now I've got

SELECT * FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
AND table_name like 'schedule_%'
ORDER BY table_name;

But for instance I need the last 7 days' record so that a date sequence shall be starting from 20121012 to 20121018. Thanks!

Clodoaldo Neto · Accepted Answer · 2012-10-19 15:57:24Z

3

SELECT *
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
    AND table_name in (
        select 'schedule_' || to_char(d, 'YYYYMMDD')
        from 
        generate_series(current_date - 7, current_date - 1, '1 day') s(d)
        )
ORDER BY table_name;

Older Postgresql versions:

SELECT *
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
    AND table_name in (
        select 'schedule_' || to_char(current_date - d, 'YYYYMMDD')
        from 
        generate_series(7, 1, -1) s(d)
        )
ORDER BY table_name;

edited Oct 19, 2012 at 15:57

answered Oct 19, 2012 at 15:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

Rock Over a year ago

Thank you Clodoaldo. I tried to_char but it gives ERROR: multiple decimal points for function "to_char". Have you tried it on your machine or should I use some other functions? I suspect it's I'm using a lower Postgresql version 8.2? Thanks!

Clodoaldo Neto Over a year ago

@Rock Added a version for older postgresql versions.

Rock Over a year ago

Thanks a lot Clodoaldo. I figured it out in another way select regexp_replace((current_date + s.a), '-', '', 'g') as dates from generate_series(-7,-1,1) as s(a); but yours is much cleaner. Thanks!

Craig Ringer · Accepted Answer · 2012-10-19 15:23:59Z

2

Use generate_series, possibly with to_char for formatting.

regress=# select generate_series(DATE '20121012', DATE '20121018', interval '1' day);
    generate_series     
------------------------
 2012-10-12 00:00:00+08
 2012-10-13 00:00:00+08
 2012-10-14 00:00:00+08
 2012-10-15 00:00:00+08
 2012-10-16 00:00:00+08
 2012-10-17 00:00:00+08
 2012-10-18 00:00:00+08
(7 rows)

answered Oct 19, 2012 at 15:23

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

Generate sequence of dates used in for loop

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related