问题
编写一个SQL语句,该语句从一个表中选择日期介于开始月份/天和结束月份/天之间的值,而不管开始日期和结束日期是如何选择的。换句话说,这是一个一年的包装问题。
输入
查询作为参数接收:
-
第1年,第2年:年的全部范围,独立于月/日组合。
-
-
Month1,Day1:一年中收集数据的开始日期。
-
-
Month2,Day2:收集数据的一年(或下一年)内的结束日。
-
上一次尝试
考虑下面的mysql代码(有效的):。
end_year=start_year+
最大(1)
符号(符号)
DATEDIFF(
日期(
concat_ws(“-”,年,月底,日底)
)
日期(
concat_ws(“-”,year,start_month,start_day)
)
)
),0
)
< /代码>
它是如何工作的,对于复杂的日期场景:
- 在当前年份中创建两个日期。
-
- 第一个日期是1900年12月22日,第二个日期是1900年2月28日。
- 计算两个日期之间的差额(以天为单位)。
-
- 如果结果为负数,则表示第二个日期的年份必须增加1。在这种情况下:
- 在当前年份中添加1。
-
- 创建一个新的结束日期:1901年2月28日。
- 检查数据的日期范围是否介于开始日期和计算的结束日期之间。
-
- 如果结果是肯定的,日期是按时间顺序提供的,不需要做任何特殊的事情。
-
这在MySQL中有效,因为日期的差异是正的或负的。在PostgreSQL中,等效功能总是返回一个正数,不管它们的相对时间顺序如何。(不过,我对减法日期的测试可能不正确。)
问题
为了让PostgreSQL考虑到开始和结束的月/日对的相对时间顺序(关于引入棘手的年度时间位移),应该如何重写以下(损坏的)代码?
选择
M.量
从
测量M
哪里
(摘录(从m.take开始的月份)>=month1和
提取(从m.take开始的天)>=day1)和
(提取(从m.take开始的月份)<=month2和
提取(从m.take算起的天数)<=day2)
< /代码>
有什么想法、意见或问题吗?
(这些日期在PHP中被预先解析为mm/dd格式。我喜欢纯PostgreSQL解决方案,但是我愿意接受关于使用PHP使问题简单化的建议。SQL存在于JasperReports调用的存储过程中,因此PHP只能接触传递到报告引擎的日期。)
更新1
以下代码几乎可以工作:
选择
(摘录(从m.take开始的年份)‘-12-12’):日期作为开始日期,
(摘录(从m.taken开始的年份)+
最大的(
0,
符号(符号)
(摘录(从m.take开始的年份)‘-12-12’):日期-
(摘录(从m.take开始的年份)-02-01')::日期))-02-01'):日期作为结束日期,
M.拍摄,
提取(从m.taken开始的年份)作为\u采取的年份
从
测量M
哪里
M.Station_id=200和
M.category_id=1和
(m.taken,m.taken)重叠
((摘录(从m.take开始年份)‘-12-12’):日期,
(摘录(从m.taken开始的年份)+
最大的(
0,
符号(符号)
(摘录(从m.take开始的年份)‘-12-12’):日期-
(摘录(从m.take开始的年份)‘-02-01’::日期))‘-02-01’::日期)
< /代码>
它产生正确的开始和结束日期约束:
开始日期;结束日期;采取;年采取
“1969-12-12”;“1970-02-01”;“1969-12-12”;1969
“1969-12-12”;“1970-02-01”;“1969-12-13”;1969
…
“1969-12-12”;“1970-02-01”;“1969-12-31”;1969
“1970-12-12”;“1971-02-01”;“1970-12-12”;1970年
< /代码>
但是,1970-01-01和1970-02-01之间的值丢失,但必须包括在内。
版本
PostgreSQL 8.4.4和PHP 5.2.10
任何起始月/日和结束月/日组合都有效,例如:
第二种组合很困难(我称之为“棘手的日期方案”),因为月末/日应该来之后年起始月/日. 也就是说,对于1900年(也显示在上面的屏幕截图中),完整日期为:
- 12月22日,一千九百到2月28日,一千九百零一
- 12月22日,一千九百零一到2月28日,一千九百零二
- …
- 12月22日,二千零七到2月28日,二千零八
- 12月22日,二千零八到2月28日,二千零九
问题
编写一个SQL语句,该语句从一个表中选择日期介于开始月份/天和结束月份/天之间的值,而不管开始日期和结束日期是如何选择的。换句话说,这是一个年度包装问题。
输入
查询作为参数接收:
- 第1年,第2年:年的全部范围,独立于月/日组合。
- Month1,Day1:一年中收集数据的开始日期。
- Month2,Day2:一年(或下一年)内收集数据的结束日期。
上一次尝试
考虑下面的mysql代码(有效的):
end_year = start_year +
greatest( -1 *
sign(
datediff(
date(
concat_ws('-', year, end_month, end_day )
),
date(
concat_ws('-', year, start_month, start_day )
)
)
), 0
)
它是如何工作的,对于复杂的日期场景:
- 在当前年份中创建两个日期。
- 第一次约会是1900年12月22日第二次约会是1900年2月28日.
- 计算两个日期之间的差额(以天为单位)。
- 如果结果为负数,则表示第二个日期的年份必须增加1。在这种情况下:
- 将1添加到当前年份。
- 创建新的结束日期:1901年2月28日.
- 检查数据的日期范围是否介于开始日期和计算的结束日期之间。
- 如果结果是肯定的,日期是按时间顺序提供的,不需要做任何特殊的事情。
这在MySQL中有效,因为日期的差异是正的或负的。在PostgreSQL中,等价功能总是返回一个正数,而不管它们的相对时间顺序如何。(不过,我对减法日期的测试可能不正确。)
问题
为了让PostgreSQL考虑到开始和结束的月/日对的相对时间顺序(关于引入棘手的年度时间位移),应该如何重写以下(损坏的)代码?
SELECT
m.amount
FROM
measurement m
WHERE
(extract(MONTH FROM m.taken) >= month1 AND
extract(DAY FROM m.taken) >= day1) AND
(extract(MONTH FROM m.taken) <= month2 AND
extract(DAY FROM m.taken) <= day2)
有什么想法、意见或问题吗?
(这些日期在PHP中被预先解析为mm/dd格式。我喜欢纯PostgreSQL解决方案,但是我愿意接受关于使用PHP使问题简单化的建议。SQL存在于JasperReports调用的存储过程中,因此PHP只能接触传递到报告引擎的日期。)
更新第1号
以下代码几乎可以工作:
select
(extract(YEAR FROM m.taken)||'-12-12')::date as start_date,
((extract(YEAR FROM m.taken)+
greatest(
0,
sign(
(extract(YEAR FROM m.taken)||'-12-12')::date -
(extract(YEAR FROM m.taken)||'-02-01')::date) ))||'-02-01')::date as end_date,
m.taken,
extract(YEAR FROM m.taken) as year_taken
from
measurement m
where
m.station_id = 200 AND
m.category_id = 1 AND
(m.taken, m.taken) OVERLAPS
((extract(YEAR FROM m.taken)||'-12-12')::date,
((extract(YEAR FROM m.taken)+
greatest(
0,
sign(
(extract(YEAR FROM m.taken)||'-12-12')::date -
(extract(YEAR FROM m.taken)||'-02-01')::date) ))||'-02-01')::date)
它产生正确的开始和结束日期约束:
start_date ;end_date ;taken ;year_taken
"1969-12-12";"1970-02-01";"1969-12-12";1969
"1969-12-12";"1970-02-01";"1969-12-13";1969
...
"1969-12-12";"1970-02-01";"1969-12-31";1969
"1970-12-12";"1971-02-01";"1970-12-12";1970
但是,值介于1970-01-01和1970-02-01缺少,但必须包括在内。
版本
PostgreSQL 8.4.4和PHP 5.2.10
