在数据库中存储年份 [英] storing year in database

问题描述

年份数据最好的PostgreSQL数据类型是什么，例如2006或1847. TEXT ， SMALLINT ， DATE ?理想情况下，我希望能够使用第二个年月日列(以 DATE 格式)查询该列.

What is the best PostgreSQL data type for year data, e.g., 2006 or 1847. TEXT, SMALLINT, DATE? Ideally, I'd like to be able to query that column with a second year-month-day column (in DATE format).

推荐答案

年份是整数，甚至以有意义的方式支持整数算术，因此 text 毫无意义.您没有月或日，因此 date 就在窗口旁边.精细手册的内容是关于<代码> smallint :

A year is an integer and even supports integer arithmetic in a meaningful way so text makes no sense. You don't have a month or day so date is right out the window. The fine manual has this to say about smallint:

smallint 类型通常仅在磁盘空间有限的情况下使用.

The smallint type is generally only used if disk space is at a premium.

这自然是将 integer 保留下来.如果您打算将此值与 date s和 timestamps 结合使用，则

That leaves integer as a natural choice. If you're planning to combine this value with dates and timestamps, then interval might make sense as the date and time functions and operators have a good understanding of intervals.

因此，这取决于您打算在此年份"中做些什么以及它实际上是什么.听起来像一个简单的 integer 是合适的，但是您可能需要花一点时间才能将其与第二年-月-日-日期"列(可能是 date 列)结合起来取决于他们需要如何合作.

So it depends on what you're planning on doing with this "year" and what it really is. Sounds like a simple integer is appropriate but you might have to muck around a bit to combine it with your "second year-month-day column" (a date column presumably) depending on how they need to work together.

这篇关于在数据库中存储年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！