YYYYMM“之间”选择日期栏？ [英] YYYYMM "between" selection on date column?
问题描述
您好，
我们目前正在将旧的ISAM应用程序转换为Java和DB2。
问题的负载我们发现报告中有用户
选项，例如选择mm / yyyy和mm / yyyy之间的所有数据。
表示：如果用户输入10/2004 - 10/2005
" 2004-10-01"和2004-10-31选择。
这不是一个真正的问题，因为我可以通过计算第一个和第二个来将日期和时间上限分别扩展到DB2日期。最后
一个月中的日期并将其填入查询参数。
或计算朱利安日期并从上面减去1天
选择值并将其用作参数。
但我很好奇这是否可以在查询中解决而不会消耗太多
很多负载在数据库服务器上。
Aselect ... where order_date> =''2004-10''和order_date< ='''2005-10''
不起作用，> =''2004-10- *''既不:-(
Bernd
-
Ja，alles meine HerrenFchrst Lichnowsky。Ooch det roochen？
Ja，auch das Rauchen。 ;Ooch im Tiergarten？"Ja，auch im Tiergarten
darf geraucht werden，meine Herren。Und so endeten die Barrikadenk？mpfe
des 18. M？rz in Berlin
Hi there,
we''re currently convert an old ISAM application to Java and DB2.
Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".
Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.
This isn''t a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.
Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.
But I''m curious if this can be solved in a query without consuming too
much load on the database server.
A "select ... where order_date >= ''2004-10'' and order_date <=''2005-10''
didn''t work, >=''2004-10-*'' neither :-(
Bernd
--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenk?mpfe
des 18. M?rz in Berlin
推荐答案
Bernd Hohmann写道：
Bernd Hohmann wrote:
您好，
我们目前正在将旧的ISAM应用程序转换为Java和DB2。
在负载中我们发现有些用户选择的报告有选择mm / yyyy和mm / yyyy之间的所有数据的问题。
意思是：如果用户输入10/2004 - 2005分之10"
" 2004-10-01"和2004-10-31选择。
这不是一个真正的问题，因为我可以通过计算第一天和最后一天来将日期和/或晚期MMYYYY日期选择扩展到DB2日期。月份并将其填入查询参数。
或者计算朱利安日期并从上面的选择值减去1天并将其用作参数。
但是我很好奇这是否可以在查询中解决，而不会在数据库服务器上消耗过多的负担。
Aselect ... where order_date> =''2004 -10''和order_date< ='''2005-10''
不起作用，> =''2004-10- *''既没有:-(
Bernd
Hi there,
we''re currently convert an old ISAM application to Java and DB2.
Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".
Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.
This isn''t a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.
Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.
But I''m curious if this can be solved in a query without consuming too
much load on the database server.
A "select ... where order_date >= ''2004-10'' and order_date <=''2005-10''
didn''t work, >=''2004-10-*'' neither :-(
Bernd
Bernd，为什么你认为系统有负载？在你做建议的时候你需要做什么。取决于你的修改包你会得到
常量表达式涉及在运行时执行一次，或者如果
你传递的是真常量并且是在一个晚期的fixpack D上B2可以在编译时计算
整个野兽（sonething我们称之为常量折叠）。
干杯
Serge
PS：欢迎回到你藏在哪一块石头上;-)
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Bernd, why do you believe there is load on the system? when doing the
math you propose. Depending on your fixpack you will either get the
constant expressions involved executed exactly once at runtime, or if
you pass true constants and are on a late fixpack DB2 may compute the
whole beast at compile-time (sonething we call "constant folding").
Cheers
Serge
PS: Welcome back from whichever stone you have been hiding under ;-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau写道：
Serge Rielau wrote:
这不是一个真正的问题，因为我可以通过计算一个月的第一天和最后一天来将下限和上限的MMYYYY日期选择扩展到DB2日期并将其填入查询参数。
或者计算Julian日期并从上面的
选择值中减去1天并将其用作参数。
This isn''t a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.
Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.
[。 ..] Bernd，为什么你认为系统有负载？在做你建议的数学时。根据你的fixpack，你将获得在运行时只执行一次的
常量表达式，或者如果你传递了真正的常量并且是在一个晚期的fixpack中DB2可能会在编译时计算整个beast -time（sonething我们称之为常数折叠）。
问题不是CPU负载，而是额外的问题
代码。附加代码迟早会意味着维护问题。
所以我必须决定害虫和霍乱。旧的ISAM可以用于
这个任务：选择... 2004_和200510之间的o_date *。
PS：欢迎从你有的石头回来一直躲在; - ）
[...] Bernd, why do you believe there is load on the system? when doing the
math you propose. Depending on your fixpack you will either get the
constant expressions involved executed exactly once at runtime, or if
you pass true constants and are on a late fixpack DB2 may compute the
whole beast at compile-time (sonething we call "constant folding").
The problem isn''t really the CPU load but the problem of additional
code. Additional code means problems in maintenance sooner or later.
So I have to decide betwen pest and cholera. The old ISAM was ok for
this task: "select ... where o_date between 200410 and 200510*".
PS: Welcome back from whichever stone you have been hiding under ;-)
非常感谢。我想我会在接下来的几周内更多地参与刷新
我的知识。
Bernd
-
" Ja，alles meine Herren" FürstLichnowsky。 Ooch det roochen？
Ja，auch das Rauchen。 Ooch im Tiergarten？ Ja，auch im Tiergarten
darf geraucht werden，meine Herren。如此顽固的死于Barrikadenk？mpfe
des 18. M？rz在柏林
Thanks a lot. I think I''ll participate more in the next weeks to refresh
my knowledge.
Bernd
--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenk?mpfe
des 18. M?rz in Berlin
Bernd Hohmann写道：
Bernd Hohmann wrote:
您好，
我们目前正在将旧的ISAM应用程序转换为Java和DB2。
在遇到的问题中我们发现有用户的报告
选项如选择mm / yyyy和mm / yyyy之间的所有数据。
意思是：如果用户输入10/2004 - 10/2005，
" 2004-10-01"和2004-10-31选择。
这不是一个真正的问题，因为我可以通过计算第一天和最后一天来将日期和/或晚期MMYYYY日期选择扩展到DB2日期。月份并将其填入查询参数。
或者计算朱利安日期并从上面的选择值减去1天并将其用作参数。
但是我很好奇这是否可以在查询中解决，而不会在数据库服务器上消耗过多的负担。
Aselect ... where order_date> =''2004 -10''和order_date< ='''2005-10''
不起作用，> =''2004-10- *''既没有:-(
Bernd
Hi there,
we''re currently convert an old ISAM application to Java and DB2.
Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".
Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.
This isn''t a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.
Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.
But I''m curious if this can be solved in a query without consuming too
much load on the database server.
A "select ... where order_date >= ''2004-10'' and order_date <=''2005-10''
didn''t work, >=''2004-10-*'' neither :-(
Bernd
看起来很难看但是
年（date_col）||''/''|| MONTH（date_col）BETWEEN''yyyy / mm' '和''yyyy / mm''
应该有效，条件是如果它是唯一的WHERE子句，则可能需要对表进行连续扫描
br />
It is ugly looking but
YEAR(date_col)||''/''||MONTH(date_col) BETWEEN ''yyyy/mm'' AND ''yyyy/mm''
should work, with the proviso that if it is the only WHERE clause, a
sequential scan of your table may be necessitated
这篇关于YYYYMM“之间”选择日期栏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
