我一定是想念明显的 [英] I must be missing the obvious
问题描述
我们使用sysdate为表X中的每条记录添加日期戳。为了查询上个月的所有表X记录（包括其最后一秒），我在上一个月和当月的第一天之间搜索
。但是对于
报告，我将报告的结束日期显示为最后一个月的b / b $ b月，因为人们认为从1到30。不是介于1和31之间。那么
这些查询的标准是什么？我可以to_char
日期戳 - 但这很慢 - 并且将datestamp'的类型更改为
varchar2是不可能的，我也不确定。我是唯一有这个问题的人吗？
Joe Powell写道：我们用sysdate用日期戳表X中的每条记录。为了查询上个月的所有表X记录，包括其最后一秒，我在最后一个月和当月的第一天之间进行搜索。但是对于
报告，我将报告的结束日期显示为最后一个月的最后一秒，因为人们认为从1到30。不是介于1和31之间。那么这些查询的标准是什么？我可以to_char
日期戳 - 但这很慢 - 并且将datestamp'的类型更改为
varchar2是不可能的，我也不确定。我是唯一有这个问题的人吗？
要想在一个月内获得所有记录，你可以说：
WHERE a_date
BETWEEN TO_DATE（''01-Jun-2004''，''DD-MON-YYYY''）
AND（TO_DATE（''' 2004年7月1日''，''DD-MON-YYYY''） - （1 /（24 * 60 * 60）））
1 /（24 * 60 * 60）如果你是一个oracle DATE列，则= 1秒。 24 =小时
白天和'60 * 60''=秒小时。
没有理由为什么索引不能工作这种情况并没有
要求使用to_char。
David Rolfe
Orinda软件
都柏林，爱尔兰
------------------------------------ ------------------------
Orinda Software制作OrindaBuild，一个Java JDBC代码生成器
www.orindasoft.com
D Rolfe< dw ************* @ orindasoft.com>在消息新闻中写道：< 40 ************** @ orindasoft.com> ...Joe Powell写道：我们使用sysdate为表X中的每条记录添加日期戳。为了查询上个月的所有表X记录，包括其最后一秒，我在最后一个月和当月的第一天之间进行搜索。但是对于
报告，我将报告的结束日期显示为最后一个月的最后一秒，因为人们认为从1到30。不是介于1和31之间。那么这些查询的标准是什么？我可以to_char
日期戳 - 但这很慢 - 并且将datestamp'的类型更改为
varchar2是不可能的，我也不确定。我是唯一有这个问题的人吗？
要在一个月内获得所有记录，你可以说：
WHERE a_date 在2004年1月半之前（'' - 2004年6月1日''，''DD-MON-YYYY''）
和（2004年7月1日''，'DD-MON-YYYY' '） - （1 /（24 * 60 * 60）））
1 /（24 * 60 * 60）= 1秒，如果你是一个oracle DATE专栏。 24 =小时
白天和''60 * 60''=秒小时。
没有理由为什么索引不能在这种情况下工作而没有
使用to_char的要求。
大卫罗尔夫
奥林达软件
爱尔兰都柏林
-------------- ----------------------------------------------
Orinda Software制作OrindaBuild，一个Java JDBC代码生成器
www.orindasoft.com
我认为你可能想看看add_month和
last_day日期函数，它们可以让你计算
从sysdata上个月
确定
月的最后一天。 add_months（sysdate，-1）的截断将为您提供上个月第一天的
。
请参阅SQL手册。我会发布示例，但我不会在家用电脑上运行Oracle。
HTH - Mark D Powell -
Mark D Powell写道：
D Rolfe< dw ******** *****@orindasoft.com>在消息新闻中写道：< 40 ************** @ orindasoft.com> ...
Joe Powell写道：
我们使用sysdate为表X中的每条记录添加日期。为了查询上个月的所有表X记录，包括其最后一秒，我在最后一个月和当月的第一天之间进行搜索。但是对于
报告，我将报告的结束日期显示为最后一个月的最后一秒，因为人们认为从1到30。不是介于1和31之间。那么这些查询的标准是什么？我可以to_char
日期戳 - 但这很慢 - 并且将datestamp'的类型更改为
varchar2是不可能的，我也不确定。我是唯一有这个问题的人吗？
要在一个月内获得所有记录，你可以说：
WHERE a_date 在2004年1月半之前（'' - 2004年6月1日''，''DD-MON-YYYY''）
和（2004年7月1日''，'DD-MON-YYYY' '） - （1 /（24 * 60 * 60）））
1 /（24 * 60 * 60）= 1秒，如果你是一个oracle DATE专栏。 24 =小时
白天和''60 * 60''=秒小时。
没有理由为什么索引不能在这种情况下工作而没有
使用to_char的要求。
大卫罗尔夫
奥林达软件
爱尔兰都柏林
-------------- ----------------------------------------------
Orinda Software制作OrindaBuild，一个Java JDBC代码生成器
www.orindasoft.com
我认为你可能想看看add_month和
last_day日期函数，它们可以让你从sysdata计算上个月的
并确定那个月的最后一天。 add_months（sysdate，-1）的trunc会给你
上个月的第一天。
我正在做他想要的假设一个月开始于：
01-Jun-04 00:00:00
和
30-Jun-04 23:59:59
他还提到SYSDATE用于填充列，
表示DATE将准确到一秒。
因为BETWEEN运算符是包含的，所以搜索表达式需要
允许报告期间结束于23:59 ：59。这个
表示结束日期必须排除''01 -Jul-04 00:00:00''。这意味着
在几秒钟内完成。您可以：
1.使用从结束日期开始减去1秒的表达式
2.使用to_date和''23：59：59' '找出报告的确切时间
期结束
3.使用BETWEEN并有额外的''<''条件排除第一个
下个月的第二个。
如果没有计算秒数，将导致报告在第一天的午夜计算
交易月分为两个月，分别是两个月。这可能会损害您年终奖金。
David Rolfe
Orinda软件
爱尔兰都柏林
We datestamp each record in table X with sysdate. In order to query
all table X records in the previous month including its last second, I
search between the first day of the last and current month. But for
reports, I show the end date of the report as the last second of last
month because humans think "from 1 to 30" not "between 1 and 31". So
what is the standard for these queries? I can to_char the
datestamp--but that is very slow--and changing the datestamp''s type to
varchar2 is not possible nor am I sure desireable. Am I the only one
with this question?
Joe Powell wrote:We datestamp each record in table X with sysdate. In order to query
all table X records in the previous month including its last second, I
search between the first day of the last and current month. But for
reports, I show the end date of the report as the last second of last
month because humans think "from 1 to 30" not "between 1 and 31". So
what is the standard for these queries? I can to_char the
datestamp--but that is very slow--and changing the datestamp''s type to
varchar2 is not possible nor am I sure desireable. Am I the only one
with this question?
To get all the records in one month you can say:
WHERE a_date
BETWEEN TO_DATE(''01-Jun-2004'',''DD-MON-YYYY'')
AND (TO_DATE(''01-Jul-2004'',''DD-MON-YYYY'') - (1/(24 * 60 * 60)))
1/(24 * 60 * 60) = 1 second if you are an oracle DATE column. 24 = hours
in day and ''60 * 60'' = seconds in hour.
There is no reason why indexes won''t work in this situation and no
requirement to use to_char.
David Rolfe
Orinda Software
Dublin, Ireland
------------------------------------------------------------
Orinda Software make OrindaBuild, A Java JDBC Code Generator
www.orindasoft.com
D Rolfe <dw*************@orindasoft.com> wrote in message news:<40**************@orindasoft.com>...Joe Powell wrote:We datestamp each record in table X with sysdate. In order to query
all table X records in the previous month including its last second, I
search between the first day of the last and current month. But for
reports, I show the end date of the report as the last second of last
month because humans think "from 1 to 30" not "between 1 and 31". So
what is the standard for these queries? I can to_char the
datestamp--but that is very slow--and changing the datestamp''s type to
varchar2 is not possible nor am I sure desireable. Am I the only one
with this question?
To get all the records in one month you can say:
WHERE a_date
BETWEEN TO_DATE(''01-Jun-2004'',''DD-MON-YYYY'')
AND (TO_DATE(''01-Jul-2004'',''DD-MON-YYYY'') - (1/(24 * 60 * 60)))
1/(24 * 60 * 60) = 1 second if you are an oracle DATE column. 24 = hours
in day and ''60 * 60'' = seconds in hour.
There is no reason why indexes won''t work in this situation and no
requirement to use to_char.
David Rolfe
Orinda Software
Dublin, Ireland
------------------------------------------------------------
Orinda Software make OrindaBuild, A Java JDBC Code Generator
www.orindasoft.com
I would think that you might want to look at the add_month and
last_day date functions which would allow you to calculate the
previous month from the sysdata and determine the last day of that
month. A trunc of the add_months(sysdate, -1) would give you the
first day of the prior month.
See the SQL manual. I would post examples but I do not run Oracle on
my home PC.
HTH -- Mark D Powell --
Mark D Powell wrote:
D Rolfe <dw*************@orindasoft.com> wrote in message news:<40**************@orindasoft.com>...Joe Powell wrote:We datestamp each record in table X with sysdate. In order to query
all table X records in the previous month including its last second, I
search between the first day of the last and current month. But for
reports, I show the end date of the report as the last second of last
month because humans think "from 1 to 30" not "between 1 and 31". So
what is the standard for these queries? I can to_char the
datestamp--but that is very slow--and changing the datestamp''s type to
varchar2 is not possible nor am I sure desireable. Am I the only one
with this question?
To get all the records in one month you can say:
WHERE a_date
BETWEEN TO_DATE(''01-Jun-2004'',''DD-MON-YYYY'')
AND (TO_DATE(''01-Jul-2004'',''DD-MON-YYYY'') - (1/(24 * 60 * 60)))
1/(24 * 60 * 60) = 1 second if you are an oracle DATE column. 24 = hours
in day and ''60 * 60'' = seconds in hour.
There is no reason why indexes won''t work in this situation and no
requirement to use to_char.
David Rolfe
Orinda Software
Dublin, Ireland
------------------------------------------------------------
Orinda Software make OrindaBuild, A Java JDBC Code Generator
www.orindasoft.com
I would think that you might want to look at the add_month and
last_day date functions which would allow you to calculate the
previous month from the sysdata and determine the last day of that
month. A trunc of the add_months(sysdate, -1) would give you the
first day of the prior month.
I am working on the assumption he wants a month that starts at:
01-Jun-04 00:00:00
and
30-Jun-04 23:59:59
He also mentioned that SYSDATE is used to populate the column, which
means the DATE will be accurate to one second.
Because the BETWEEN operator is inclusive the search expression needs to
allow for the fact that the reporting period ends at 23:59:59. This
means that the end date must exclude ''01-Jul-04 00:00:00''. This implies
working in seconds. You can either:
1. Use an expression that substracts 1 second from the end date
2. Use to_date and ''23:59:59'' to figure out the exact time the reporting
period ends
3. Use BETWEEN and have an additional ''<'' condition to exclude the first
second of the next month.
Failure to account for seconds will lead to reports that count
transactions at midnight on the first day of a month as being in two
seperate months. This can harm your end of year bonus.
David Rolfe
Orinda Software
Dublin, Ireland
这篇关于我一定是想念明显的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
