SQL查询按天/月/年/日和月/日/年等进行搜索 [英] SQL query to search by day/month/year/day&month/day&year etc

问题描述

我有一个带有事件的PostgreSQL数据库。每个事件都有一个日期时间或一个间隔。公用数据存储在 events 表中，日期存储在 events_dates （ datetime 字段）或 events_intervals （ starts_date ， ends_date 都是 date 字段）。

采样日期时间事件

• 我出生于1930-06-09


• 我于1950-07-12取得了驾照


• 圣诞节是1900-12-24（1900是为每年一次的重复活动而保留）

采样间隔事件

• 我将从2011-06-09到2011-07-23休假

现在我有一个用户想要查找这些事件。他们将能够使用 from 和 to 字段填写表格，并在这些字段中输入完整日期，一个或两个字段中的日，月，年，日和月，日和年，月和年。

示例查询

• 从5月3日到2012年12月21日将查找5月3日至12月21日之间的事件，最长年份是2012年。


• 从第3天到第3天15将查找每月和每年的第3天到第15天之间的事件


• 从第3天开始，将查找在每年和每年的第3天之间的事件（如果 from 为空，而 to 不是）


• 从5月3日到6月将查找事件在每年的5月3日到六月的最后一天之间


• 等。

关于

我们想到的一些事情

• 从写所有可能的，至和日/月/年组合-而非主表


• 将日期作为字符串进行比较，例如输入： ____- 06 -__ 其中 _ 是通配符-我不必生成所有可能的组合，但

您可以编写可维护的查询，另外通过以下方法可以快速进行查询：



解决方案

使用pg / temporal扩展名：

https ：//github.com/jeff-davis/PostgreSQL-Temporal

 使用gist（句点创建事件索引（开始日期，结束日期））； 
 
 select * 
来自事件
，其中period（start_date，end_date）@> ：日期; 
 
 select * 
来自事件
，其中period（start_date，end_date）&& period（：start，：end）; 
  

您甚至可以使用它来禁止重叠作为表约束：

 更改表事件
添加约束overlay_excl 
排除使用gist（period（start_date，end_date）WITH&&）; 
  

---

写所有可能的从，到以及日/月/年组合-不是维护表

实际上它比您想像的要容易维护，例如：

  select * 
来自事件
加入generate_series（：start_date，：end_date，：interval）作为日期时间
在start_date< = datetime和datetime<结束日期; 
  

但是最好使用上述期间类型。

I have a PostgreSQL database with events. Each event has a datetime or an interval. Common data are stored in the events table and dates are stored in either events_dates (datetime field) or events_intervals (starts_date, ends_date both are date fields).

Sample datetime events

• I was born on 1930-06-09
• I got my driver's license on 1950-07-12
• Christmas is on 1900-12-24 (1900 is reserved for yearly reoccuring events)

Sample interval events

• I'll be on vacation from 2011-06-09 till 2011-07-23

Now I have a user that will want to look up these events. They will be able to fill out a form with from and to fields and in those fields they can enter full date, day, month, year, day and month, day and year, month and year in one or both fields.

Sample queries

• From May 3 to 2012 December 21 will look for events between May 3 and December 21 whose max year is 2012
• From day 3 to day 15 will look for events between the 3rd and 15th day of every month and year
• From day 3 will look for events on the 3rd day of every month and year (same if from is empty and to is not)
• From May 3 to June will look for events between May 3 and last day of June of every year
• etc.

Any tips on how to write a maintanable query (it doesn't necessarily have to be fast)?

Some things that we thought of

• write all possible from, to and day/month/year combinations - not maintable
• compare dates as strings e.g. input: ____-06-__ where _ is a wildcard - I wouldn't have to generate all possible combinations but this doesn't work for intervals

解决方案

You can write maintainable queries that additionally are fast by using the pg/temporal extension:

https://github.com/jeff-davis/PostgreSQL-Temporal

create index on events using gist(period(start_date, end_date));

select *
from events
where period(start_date, end_date) @> :date;

select *
from events
where period(start_date, end_date) && period(:start, :end);

You can even use it to disallow overlaps as a table constraint:

alter table events
add constraint overlap_excl
exclude using gist(period(start_date, end_date) WITH &&);

---

write all possible from, to and day/month/year combinations - not maintable

It's actually more maintainable than you might think, e.g.:

select *
from events
join generate_series(:start_date, :end_date, :interval) as datetime
on start_date <= datetime and datetime < end_date;

But it's much better to use the above-mentioned period type.

这篇关于SQL查询按天/月/年/日和月/日/年等进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！