469,890 Members | 2,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

I must be missing the obvious

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?
Jul 19 '05 #1
14 4939


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

Jul 19 '05 #2
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 --
Jul 19 '05 #3


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

Jul 19 '05 #4
> Hello, Joe,

near 09:03 30-Jun from jo********@lmco.com accepted:
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?
There are no "standard" how you can see... The best practicies instead.

Assume

CREATE TABLE T1(d1 date, n1 number);

In order to receive previous month's data I'm using this statement

SELECT SUM(n1)
FROM T1
WHERE d1 >= trunc(add_months(sysdate,1),'month')

---------------------------------------^ of course:
WHERE d1 >= trunc(add_months(sysdate,-1),'month')
AND d1 < trunc(sysdate, 'month');

Doing so you can get the data without thinking about measuring precision
(second or fraction of it).
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.


cast (if you really using timestamp datatype), to_char makes it possible
(not desireable of course).
Am I the only one with this question?


Anyone who makes reports has solved this question, I think.

--
wbr,
Wit.

Jul 19 '05 #5
Hello, Joe,

near 09:03 30-Jun from jo********@lmco.com accepted:
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?
There are no "standard" how you can see... The best practicies instead.

Assume

CREATE TABLE T1(d1 date, n1 number);

In order to receive previous month's data I'm using this statement

SELECT SUM(n1)
FROM T1
WHERE d1 >= trunc(add_months(sysdate,1),'month')
AND d1 < trunc(sysdate, 'month');

Doing so you can get the data without thinking about measuring precision
(second or fraction of it).
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.
cast (if you really using timestamp datatype), to_char makes it possible
(not desireable of course).
Am I the only one with this question?


Anyone who makes reports has solved this question, I think.

--
wbr,
Wit.
Jul 19 '05 #6
D Rolfe <dw*************@orindasoft.com> wrote in message news:<40**************@orindasoft.com>...
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


David, apparently I did not make my intent clear. I am suggesting
that the add_month and last_date functions could be substituted into
the code to calculate the prior month off of sysdate so that code
changes would not be required. Just run the code in the current month
and all the rows for the prior month would be targeted.

Also if you want all the rows for a month you can ignore the time
component of a date column if you target the month component of the
date column. Example:

1 select fld1, fld2, to_char(fld3,'YYYYMMDD HH24:MI:SS') fld3, fld4
2* from marktest
UT1 > /

FLD1 FLD2 FLD3 FLD4
---------- ---------- ----------------- --------
one 1 20040601 00:00:00 one
two 1 20040615 00:00:00 two
three 1 20040630 00:00:00 three
four 1 20040701 00:00:00 four
five 5 20040531 23:59:59 five

1 select * from marktest
2* where to_char(fld3,'MON') =
to_char(trunc(add_months(sysdate,-1)),'MON')
UT1 > /

FLD1 FLD2 FLD3 FLD4
---------- ---------- --------- --------
one 1 01-JUN-04 one
two 1 15-JUN-04 two
three 1 30-JUN-04 three

If the date column is indexed I would not use this last method but
would place the functions to the right of the relational operators on
the input variables which I am suggesting is just
add_months(trunc(sysdate), -1), i.e., first of prior month.

1 select to_char(add_months(trunc(sysdate), -1),'YYYYMMDD
HH24:MI:SS')
2* ,sysdate from dual
UT1 > /

TO_CHAR(ADD_MONTH SYSDATE
----------------- ---------
20040601 00:00:00 01-JUL-04

You can then use < the first of the current month, i.e.,
trunc(sysdate) and the time component is covered.

HTH -- Mark D Powell --
Jul 19 '05 #7
I appreciate your responses. I've used the following (I believe
similar) clauses:

WHERE datestamp >= LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND datestamp < LAST_DAY(ADD_MONTHS(sysdate,-1))+1

Or (after altering the nls_date_format)

WHERE datestamp BETWEEN LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND LAST_DAY(ADD_MONTHS(sysdate,-1))||'235959'

Neither of these queries is particularly elegant in syntax, but they
may be as good as it gets. I posted to find out alternative methods,
particularly if some truly simple syntax is possible. I still don't
understand the following. If I query:

'select max(datestamp)from X' with nls_date_format = 'DD-MON-YY'

I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?

Adding a to_date() around the date or using "between" does not change
the results. Again, thanks for your time.
Jul 19 '05 #8
Hello, Joe,

[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?


It's simple. Really your first query gets all the JUNE records. The second
query gets record with datestamp less than '01-JUN-04 00:00:00' and records
with datestamp equal to '01-JUN-2004 00:00:00' (not with 01-JUN-04 with some
time portion). Again, '01-JUN-04 14:14:14' is greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.

--
wbr,
Wit.
Jul 19 '05 #9

"Wit Serdakovskij" <wit_no@spam_dba.kiev.ua> wrote in message
news:20********************@tormoz.net...
Hello, Joe,

[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?
It's simple. Really your first query gets all the JUNE records. The second
query gets record with datestamp less than '01-JUN-04 00:00:00' and

records with datestamp equal to '01-JUN-2004 00:00:00' (not with 01-JUN-04 with some time portion). Again, '01-JUN-04 14:14:14' is greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.

--
wbr,
Wit.


Don't use an implicit conversion of a string to a date! Use to_date()
instead. You have bug in your code just waiting to bite you.
Jim
Jul 19 '05 #10
[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?
It's simple. Really your first query gets all the JUNE records. The
second query gets record with datestamp less than '01-JUN-04 00:00:00'
and records with datestamp equal to '01-JUN-2004 00:00:00' (not with
01-JUN-04 with some time portion). Again, '01-JUN-04 14:14:14' is
greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.


[...]
Don't use an implicit conversion of a string to a date! Use to_date()
instead. You have bug in your code just waiting to bite you.


Agreed. That's just a notation from replied message...
--
wbr,
Wit.
Jul 19 '05 #11
I appreciate your responses. I've used the following (I believe
similar) clauses:

WHERE datestamp >= LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND datestamp < LAST_DAY(ADD_MONTHS(sysdate,-1))+1

Or (after altering the nls_date_format)

WHERE datestamp BETWEEN LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND LAST_DAY(ADD_MONTHS(sysdate,-1))||'235959'

Neither of these queries is particularly elegant in syntax, but they
may be as good as it gets. I posted to find out alternative methods,
particularly if some truly simple syntax is possible. I still don't
understand the following. If I query:

'select max(datestamp)from X' with nls_date_format = 'DD-MON-YY'

I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?

Adding a to_date() around the date or using "between" does not change
the results. Again, thanks for your time.
Jun 27 '08 #12
Hello, Joe,

[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?
It's simple. Really your first query gets all the JUNE records. The second
query gets record with datestamp less than '01-JUN-04 00:00:00' and records
with datestamp equal to '01-JUN-2004 00:00:00' (not with 01-JUN-04 with some
time portion). Again, '01-JUN-04 14:14:14' is greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.

--
wbr,
Wit.
Jun 27 '08 #13

"Wit Serdakovskij" <wit_no@spam_dba.kiev.uawrote in message
news:20********************@tormoz.net...
Hello, Joe,

[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does

'select count(1) from X where datestamp < '01-JUL-04' return 10 but

'select count(1) from X where datestamp <= '30-JUN-04' returns 7?

It's simple. Really your first query gets all the JUNE records. The second
query gets record with datestamp less than '01-JUN-04 00:00:00' and
records
with datestamp equal to '01-JUN-2004 00:00:00' (not with 01-JUN-04 with
some
time portion). Again, '01-JUN-04 14:14:14' is greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.

--
wbr,
Wit.
Don't use an implicit conversion of a string to a date! Use to_date()
instead. You have bug in your code just waiting to bite you.
Jim
Jun 27 '08 #14
[...]
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does
>
'select count(1) from X where datestamp < '01-JUL-04' return 10 but
>
'select count(1) from X where datestamp <= '30-JUN-04' returns 7?
It's simple. Really your first query gets all the JUNE records. The
second query gets record with datestamp less than '01-JUN-04 00:00:00'
and records with datestamp equal to '01-JUN-2004 00:00:00' (not with
01-JUN-04 with some time portion). Again, '01-JUN-04 14:14:14' is
greater than '01-JUN-04'.

[...]

Be careful with DD-MON-YY. Use DD-MON-RR (or DD-MON-YYYY) instead.
[...]
Don't use an implicit conversion of a string to a date! Use to_date()
instead. You have bug in your code just waiting to bite you.
Agreed. That's just a notation from replied message...
--
wbr,
Wit.
Jun 27 '08 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ctal | last post: by
1 post views Thread by Dominick Baier | last post: by
reply views Thread by JohnR | last post: by
2 posts views Thread by Steven D'Aprano | last post: by
77 posts views Thread by Ville Vainio | last post: by
8 posts views Thread by =?Utf-8?B?cDNqb2hu?= | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.