英文:
Populating Multi-Year Calendar Table using SQL
问题
我想填充一个多年份的日历表，并根据日期计算多个字段的值。我正在在Postgres上运行这个脚本，即使我将start_date设置为2023-02-16，end_date设置为2023-02-17，它运行起来也非常慢。请帮助找出问题所在。
英文:
I want to populate a multi-year calendar table and calculate the value of multiple fields based on the date. I'm running this script on Postgres, it is taking forever to run even if I pass start_date as 2023-02-16 and end_date as 2023-02-17. Please help with what is wrong here.
答案1
得分: 0
使用generate_series()的示例：
INSERT INTO t1(....)
SELECT
(g.d)::date AS 日期,
EXTRACT(year FROM g.d) AS 年,
EXTRACT(month FROM g.d) AS 月,
EXTRACT(day FROM g.d) AS 日,
EXTRACT(quarter FROM g.d) AS 季度,
EXTRACT(week FROM g.d) AS 周,
EXTRACT(isodow FROM g.d) AS ISO星期,
EXTRACT(doy FROM g.d) AS 年内天数,
EXTRACT(isoyear FROM g.d) AS ISO年
FROM generate_series('1970-01-01 00:00:00'::timestamp without time zone, '2049-12-31 00:00:00'::timestamp without time zone, '1 day'::interval) g(d)
ORDER BY g.d;
只需更改表名t1和您需要的列。在我的笔记本电脑上，生成和插入29220个日期只需0.1秒。
英文:
Example using generate_series():
INSERT INTO t1(....)
SELECT
(g.d)::date AS date,
EXTRACT(year FROM g.d) AS year,
EXTRACT(month FROM g.d) AS month,
EXTRACT(day FROM g.d) AS day,
EXTRACT(quarter FROM g.d) AS quarter,
EXTRACT(week FROM g.d) AS week,
EXTRACT(isodow FROM g.d) AS isodow,
EXTRACT(doy FROM g.d) AS doy,
EXTRACT(isoyear FROM g.d) AS isoyear
FROM generate_series('1970-01-01 00:00:00'::timestamp without time zone, '2049-12-31 00:00:00'::timestamp without time zone, '1 day'::interval) g(d)
ORDER BY g.d;
Just change the table name t1 and the columns you need. It take 0.1 second on my laptop to generate and insert 29220 dates.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论