如何在postgres中存储具有不同精度级别的日期？ [英] How to store dates with different levels of precision in postgres?

问题描述

我将数据存储在Postgres中PubMed的期刊论文上，并且想要存储该论文的出版日期。但是，有时日期为仅一年（请参见 PubDate 字段），有时是一个月和一年，有时是一天，一个月和一年。

I am storing data on journal papers from PubMed in Postgres, and I want to store the publication date of the paper. However, sometimes the date is just a year (see the PubDate field), sometimes it is a month and a year, and sometimes it is a day, month and year.

将这些数据存储在数据库中的明智方法是什么？

What's a sensible way to store this data in my database?

如果我使用Postgres日期字段，则显然也需要指定日期和月份，但是在某些情况下我需要发明它们，而我不这样做不喜欢丢失有关日期精度的信息的想法。

If I use a Postgres date field, I obviously need to specify day and month as well, but I'll need to invent them in some cases, and I don't like the idea of losing the information about the date's precision.

出于我的目的，我认为我只需要年份，但我不知道对于将来所有可能的用途都是如此。

For my purposes I think I will only need the year, but I don't know that to be true for all possible future purposes.

也许我应该只用 day ， month 和 year ，然后如果需要我可以随时将它们转换为日期字段？

Perhaps I should just have text fields for day, month and year, and then I can always convert them to a date field in the future if I need them?

推荐答案

我会将其存储为日期并存储精度。

I would store it as date and store the precision as well.

例如：

CREATE TYPE date_prec AS ENUM ('day', 'month', 'year');

CREATE TABLE pub (
   pub_id integer PRIMARY KEY,
   pub_date date NOT NULL,
   pub_date_prec date_prec NOT NULL
);

然后您可以像这样查询表：

Then you can query the table like this:

SELECT pub_id, date_trunc(pub_date_prec::text, pub_date)::date FROM pub;

忽略任何随机 pub_date 中的日期和月份值。

to ignore any “random” day and month values in pub_date.

这篇关于如何在postgres中存储具有不同精度级别的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！