PostgreSQL中的YYYY-MM列类型

Question

我需要在一个表格中关联到月份和用户的值，并想要对其进行查询。我不知道是否有适用于这种需求的列数据类型。如果没有，我应该：

• 创建一个字符串字段并构建年月串联（2017-01）
• 创建一个整数字段并构建年月串联（201701）
• 创建两个列（一个年份和一个月份）
• 在月初创建一个日期列（2017-01-01 00:00:00）
• 其他方式？

目标是运行类似以下的查询（伪SQL）：

SELECT val FROM t WHERE year_month = THIS_YEAR_MONTH and user_id='adc1-23...';

- rap-2-h

---

使用日期列，并添加一个date_resolution列，该列指示日期的分辨率。然后根据此进行日期计算。 - Neil McGuigan

Answer 1

我建议不要过于深思熟虑问题，只需使用每月的第一个日期或时间。Postgres拥有丰富的针对日期的特定函数——从date_trunc()到age()再到+ interval——来支持日期操作。

您可以轻松地将它们转换为所需格式、获取两个值之间的差异等等。

如果您将查询表述为：

where year_month = date_trunc('month', now()) and user_id = 'adc1-23...'

那么它可以轻松地利用索引(user_id, year_month)或(year_month, user_id)。

- Gordon Linoff

Answer 2

如果您希望以YYYY-MM格式显示值，则可以使用to_char(your_datatime_colum,'YYYY-MM')

例如:

SELECT to_char(now(),'YYYY-MM') as year_month

- Stepel

Answer 3

列类型应该是日期（DATE）在date_column中，而提取函数（extract function）是查询中满足您列出的要求的正确方法：

SELECT to_char(date_column,'YYYY_MM') as year_month, to_char(now(),'Month yyyy') as year_month2, extract(MONTH from now()) as current_month,
           concat('Q', extract(QUARTER from now())) as current_quarter, extract(YEAR from now()) as current_year

我已经添加了一个名为date_column的示例列和now()函数，如果您想在查询之前进行测试。 更多文档请参考： Postgres extract function

- zhrist

---

你是否在错误的帖子中发布了你的答案？该问题是在2017年提出的，并得到了一个被接受的好答案。然而，你的回答并没有回答这里所问的问题。 - Thorsten Kettner

@ThorstenKettner，我觉得我的解释和方法不同而且更恰当。 - zhrist

通过不回答问题来回答问题？问题是如何存储一个月。你的回答是可以从日期列或时间戳中获取年份、月份和季度的几种方法。你看到这并没有回答问题。这就是为什么我认为你可能在错误的主题中发布了你的答案，并想回答完全不同的请求。我建议你删除这个答案。 - Thorsten Kettner

@ThorstenKettner，我不明白你的意思。我已经说过，存储值应该是在日期类型的列中，并且在查询中可以使用EXTRACT函数。这不是其他答案中提到的to_char()和date_trunc()。 - zhrist

你显然理解了我的观点，因为你在回答中添加了一条备注，建议他们使用日期列来表示月份。Gordon Linoff在2017年的被接受的答案中进一步指出，应该将每个月的第一天存储下来。他提到如何查询数据，仍然可以从日期列的索引中获益，因此我们不必对该列应用任何函数。所以，是的，现在有了你的补充，你终于回答了这个问题。你建议存储任何日期并使用EXTRACT是一个有效的方法，但Gordon的建议在性能方面更好。 - Thorsten Kettner

@ThorstenKettner 好的，至少您不应该降低答案，而且我们已经将其记录为一种选项。那是我的意图，并且我改进了更清楚地涉及您的建议。 - zhrist