如何在 PostgreSQL 中生成月份列表? [英] How to generate Month list in PostgreSQL?

问题描述

我有一个表 A 与 startdate 列是 TIMESTAMP WITHOUT TIME ZONE 我需要编写一个查询/函数来生成一个列表从列的 MIN 值到列的 MAX 值的月数.

I have a table A with startdate column which is TIMESTAMP WITHOUT TIME ZONE I need to write a query/function that generate a list of months from the MIN value of the column till MAX value of the column.

例如:

startdate
2014-12-08
2015-06-16
2015-02-17

将生成一个列表:(Dec-14,Jan-15,Feb-15,Mar-15,Apr-15,May-15,Jun-15)

我该怎么做?我从来没有使用过 PostgreSQL 来生成不存在的数据......它总是在数据库中找到正确的数据......任何想法如何做到这一点?它在查询中可行吗?

How do I do that? I never used PostgreSQL to generate data that wasn't there... it always has been finding the correct data in the DB... any ideas how to do that? Is it doable in a query?

推荐答案

您可以使用 generate_series() 函数生成数据序列:

You can generate sequences of data with the generate_series() function:

SELECT to_char(generate_series(min, max, '1 month'), 'Mon-YY') AS "Mon-YY"
FROM (
  SELECT date_trunc('month', min(startdate)) AS min, 
         date_trunc('month', max(startdate)) AS max
  FROM a) sub;

这会以漂亮的格式为每个月生成一行.如果你想把它当作一个列表，你可以将它们全部聚合在一个外部查询中:

This generates a row for every month, in a pretty format. If you want to have it like a list, you can aggregate them all in an outer query:

SELECT string_agg("Mon-YY", ', ') AS "Mon-YY list"
FROM (
  -- Query above
) subsub;

此处为 SQLFiddle

这篇关于如何在 PostgreSQL 中生成月份列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！