生成for循环中使用的日期序列 [英] Generate sequence of dates used in for loop

问题描述

我有一系列按日期划分并以以下格式命名的表：

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

public.schedule_20121018

是否有一种方法可以在上述 20121018 模式，以便可以通过 information_schema.tables ？

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?

现在我已经

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

但是例如，我需要最近7天的记录，以便日期序列应从 20121012 到 20121018 。谢谢！

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

推荐答案

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;

较旧的Postgresql版本：

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;

这篇关于生成for循环中使用的日期序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！