YYYY-MM column type in PostgreSQL

Question

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...';

Answer 1

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).