PostgreSQL中的YYYY-MM列类型 [英] YYYY-MM column type in PostgreSQL

问题描述

我需要一个与月份和表中的用户相关联的值.我想对其执行查询.我不知道是否有针对此类需求的列数据类型.如果没有，我应该:

• 创建字符串字段并建立年月串联(2017-01)
• 创建一个int字段并建立年月串联(201701)
• 创建两列(一年零一个月)
• 在月初(2017-01-01 00:00:00)创建日期列
• 还有别的吗?

目标是运行类似(pseudo-SQL)的查询:

  SELECT VAL FROM t WHERE year_month = THIS_YEAR_MONTH并且user_id ='adc1-23 ...'; 

解决方案

我建议不要过分思考问题，而应该使用每月的第一个日期/时间.Postgres具有许多特定于日期的功能-从 date_trunc()到 age()到 + interval -支持日期.

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

如果您的查询短语为:

 其中year_month = date_trunc('month'，now())和user_id ='adc1-23 ...' 

然后，它可以轻松利用(用户ID，year_month)或(year_month，user_id)上的索引.

I need to a value associated to a month and a user in a table. And I want to perform queries on it. I don't know if there is a column data type for this type of need. If not, should I:

• Create a string field and build year-month concatenation (2017-01)
• Create a int field and build year-month concatenation (201701)
• Create two columns (one year and one month)
• Create a date column at the beginning of the month (2017-01-01 00:00:00)
• Something else?

The objective is to run queries like (pseudo-SQL):

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

解决方案

I would suggest not thinking too hard about the problem and just using the first date/time of the month. Postgres has plenty of date-specific functions -- from date_trunc() to age() to + interval -- to support dates.

You can readily convert them to the format you want, get the difference between two values, and so on.

If you phrase your query as:

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

Then it can readily take advantage of an index on (user_id, year_month) or (year_month, user_id).

这篇关于PostgreSQL中的YYYY-MM列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！