按日期范围(相同的月份和日期)对年份进行筛选

I have a PostgreSQL database with a table holding dates. Now I need to find all rows within the date range 15/02 until 21/06 (day/month) across all years.

我有一个PostgreSQL数据库，其中有一个表保存日期。现在我需要找到日期范围为15/02到21/06(天/月)的所有行。

Example result:

结果示例:

1840-02-28
1990-06-21
1991-02-15
1991-04-25
1992-05-30
1995-03-04
1995-04-10
2001-02-03
2010-04-06

5 个解决方案

#1

Assuming (with a leap of faith) that you want dates between certain days of the year regardless of the year (like if you're sending out a batch of birthday cards or something), you can set up a test with this:

假设你想在一年中的某些日子里约会，而不考虑具体的日期(比如你送了一批生日贺卡或别的什么)，你可以用以下方法进行测试:

CREATE TABLE d (dt date);
COPY d FROM STDIN;
1840-02-28
1990-06-21
1991-02-15
1991-04-25
1992-05-30
1995-03-04
1995-04-10
2001-02-03
2010-04-06
\.

And you can use "row value constructors" to easily select the desired range:

您可以使用“行值构造函数”轻松地选择所需的范围:

SELECT * FROM d
  WHERE (EXTRACT(MONTH FROM dt), EXTRACT(DAY FROM dt))
           BETWEEN (2, 15) AND (6, 21);

Which yields:

收益率:

     dt     
------------
 1840-02-28
 1990-06-21
 1991-02-15
 1991-04-25
 1992-05-30
 1995-03-04
 1995-04-10
 2010-04-06
(8 rows)

#2

Use a WHERE clause with the BETWEEN operator. See:

使用WHERE子句和BETWEEN操作符。看到的:

http://www.postgresql.org/docs/current/static/functions-comparison.html#FUNCTIONS-COMPARISON

http://www.postgresql.org/docs/current/static/functions-comparison.html FUNCTIONS-COMPARISON

and:

和:

http://www.postgresql.org/docs/current/static/sql-select.html http://www.postgresql.org/docs/current/static/tutorial.html

http://www.postgresql.org/docs/current/static/sql-select.html http://www.postgresql.org/docs/current/static/tutorial.html

If that doesn't help, please expand your question with:

如果没有帮助，请扩展你的问题:

• The structure of the table(s) you're working with, either from psql's \d tablename command or the original CREATE TABLE statements;
• 您正在处理的表的结构，无论是来自psql的\d tablename命令，还是原始的CREATE table语句;
• Some sample contents
• 一些样例内容
• The query you're having problems with
• 有问题的查询
• Expected results
• 预期结果

#3

You can use following syntax.

可以使用以下语法。

SELECT * FROM tableName WHERE dateColumnName BETWEEN '2012.01.01' AND '2012.08.14';

在“2012.01.01”和“2012.08.14”之间的dateColumnName中选择*;

Just replace following;

只是替换后;

tableName       - Name of the table you are going to access
dateColumnName  - Name of the column whch contains dates
2012.08.1       - Start date
2012.08.21      - End date 

When entering the two dates, carefully examine the example above. Enter in the same format, and enclose them inside ''s.

在输入两个日期时，请仔细检查上面的示例。以相同的格式输入，并将其封装在“s”中。

If you replace * mark with a column name, you can filter out values of that column only.

如果用列名替换* mark，则只能过滤该列的值。

Hope that helps..

希望帮助. .

#4

I am pretty sure, @kgrittn's interpretation of the question is accurate and I love his elegant use of a row constructors. Even more so after I have tested a couple of alternatives, none of which could match the performance:

我很确定，@kgrittn对这个问题的解释是准确的，我喜欢他对行构造函数的优雅使用。更重要的是，在我测试了几个替代方案之后，没有一个能与性能相匹配:

Tested with a real life table of 65426 rows; 32107 qualified. PostgreSQL 9.1.4, best of five with EXPLAIN ANALYZE:

使用实际的65426行表进行测试;32107合格。PostgreSQL 9.1.4，与EXPLAIN analysis合二为一:

SELECT * FROM tbl
WHERE  to_char(data, 'MMDD') BETWEEN '0215' AND '0621';

Total runtime: 251.188 ms

总运行时间:251.188 ms

SELECT * FROM tbl
WHERE  to_char(data, 'MMDD')::int BETWEEN 215 AND 621;

Total runtime: 250.965 ms

总运行时间:250.965 ms

SELECT * FROM tbl
WHERE  to_char(data, 'MMDD') COLLATE "C" BETWEEN '0215' AND '0621';

Total runtime: 221.732 ms
String comparison is faster with the "non-locale" C - more in the manual about collation support.

总运行时间:221.732 ms字符串比较快于“非本地”C -更多关于排序支持的手册。

SELECT * FROM tbl
WHERE  EXTRACT(MONTH FROM data)*100 + EXTRACT(DAY FROM data)
       BETWEEN 215 AND 621;

Total runtime: 209.965 ms

总运行时间:209.965 ms

SELECT * FROM tbl
WHERE  EXTRACT(MONTH FROM data) BETWEEN 3 AND 5
OR     EXTRACT(MONTH FROM data) = 2 AND EXTRACT(DAY FROM data) >= 15
OR     EXTRACT(MONTH FROM data) = 6 AND EXTRACT(DAY FROM data) <= 21;

Total runtime: 160.169 ms

总运行时间:160.169 ms

SELECT * FROM tbl
WHERE  EXTRACT(MONTH FROM data) BETWEEN 2 AND 6
AND    CASE EXTRACT(MONTH FROM data) 
       WHEN 2 THEN EXTRACT(DAY FROM data) >= 15
       WHEN 6 THEN EXTRACT(DAY FROM data) <=21
       ELSE TRUE END;

Total runtime: 147.390 ms

总运行时间:147.390 ms

SELECT * FROM tbl
WHERE  CASE EXTRACT(MONTH FROM data) 
       WHEN 3 THEN TRUE
       WHEN 4 THEN TRUE
       WHEN 5 THEN TRUE
       WHEN 2 THEN EXTRACT(DAY FROM data) >= 15
       WHEN 6 THEN EXTRACT(DAY FROM data) <= 21
       ELSE FALSE END;

Total runtime: 131.907 ms

总运行时间:131.907 ms

@Kevin's solution with row constructors:

@Kevin的行构造函数解决方案:

SELECT * FROM tbl
WHERE (EXTRACT(MONTH FROM data), EXTRACT(DAY FROM data))
       BETWEEN (2, 15) AND (6, 21);

Total runtime: 125.460 ms
Chapeau.

总运行时间:125.460 ms。

---

Faster with functional index

The only way to beat that is with indexes. None of the queries above can use a plain index on data. However, if read performance is crucial (and for a small cost on write performance) you can resort to a functional index:

唯一的解决方法是使用索引。上面的查询都不能对数据使用纯索引。但是，如果读性能很重要(并且写性能的成本很低)，可以使用函数索引:

CREATE INDEX ON tbl(EXTRACT(MONTH FROM data), EXTRACT(DAY FROM data));

SELECT * FROM tbl
WHERE (EXTRACT(MONTH FROM data), EXTRACT(DAY FROM data))
       BETWEEN (2, 15) AND (6, 21);

Total runtime: 85.895 ms

总运行时间:85.895 ms

And that's where I can finally beat Kevin's query by a hair: with a single column index instead of the multi-column index needed in his case.

这就是我最终击败Kevin查询的地方:用一个列索引而不是他需要的多列索引。

CREATE INDEX ON tbl(
CAST(EXTRACT(MONTH FROM data) * 100 + EXTRACT(DAY FROM data) AS int));

SELECT * FROM tbl
WHERE  (EXTRACT(MONTH FROM data) * 100 + EXTRACT(DAY FROM data))::int
       BETWEEN 215 AND 621;

Total runtime: 84.215 ms

总运行时间:84.215 ms

#5

You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type. Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function.

您可以使用简单的条件>=和<=或类似或在/之间使用，但诀窍是了解您的确切数据类型。有时日期字段包含时间，这就是查询出错的地方，因此建议使用一些与日期相关的函数来删除时间问题。在SQL Server通用函数中要做的就是datediff函数。