I must be missing the obvious

[Joe Powell]

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?

[D Rolfe]

Re: I must be missing the obvious



Joe Powell wrote:[color=blue]
> 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?[/color]

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

https://www.orindasoft.com

[Mark D Powell]

Re: I must be missing the obvious

D Rolfe <dwrolfeFRUITBA T@orindasoft.co m> wrote in message news:<40E2E63F. 9090805@orindas oft.com>...[color=blue]
> Joe Powell wrote:[color=green]
> > 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?[/color]
>
> 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[/color]

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(sysd ate, -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 --

[D Rolfe]

Re: I must be missing the obvious



Mark D Powell wrote:
[color=blue]
> D Rolfe <dwrolfeFRUITBA T@orindasoft.co m> wrote in message news:<40E2E63F. 9090805@orindas oft.com>...
>[color=green]
>>Joe Powell wrote:
>>[color=darkred]
>>>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?[/color]
>>
>>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[/color]
>
>
> 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(sysd ate, -1) would give you the
> first day of the prior month.
>[/color]

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

[Waldhausen]

Re: I must be missing the obvious

> Hello, Joe,[color=blue]
>
> near 09:03 30-Jun from joe.powell@lmco .com accepted:
>[color=green]
> > 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?[/color]
>
> 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_month s(sysdate,1),'m onth')[/color]
---------------------------------------^ of course:
WHERE d1 >= trunc(add_month s(sysdate,-1),'month')
[color=blue]
> AND d1 < trunc(sysdate, 'month');
>
> Doing so you can get the data without thinking about measuring precision
> (second or fraction of it).
>[color=green]
> > 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.[/color]
>
> cast (if you really using timestamp datatype), to_char makes it possible
> (not desireable of course).
>[color=green]
> > Am I the only one with this question?[/color]
>
> Anyone who makes reports has solved this question, I think.
>
> --
> wbr,
> Wit.[/color]

[Waldhausen]

Re: I must be missing the obvious

Hello, Joe,

near 09:03 30-Jun from joe.powell@lmco .com accepted:
[color=blue]
> 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?[/color]

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_month s(sysdate,1),'m onth')
AND d1 < trunc(sysdate, 'month');

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

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

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

--
wbr,
Wit.

[Mark D Powell]

Re: I must be missing the obvious

D Rolfe <dwrolfeFRUITBA T@orindasoft.co m> wrote in message news:<40E3BC89. 4000605@orindas oft.com>...[color=blue]
> Mark D Powell wrote:
>[color=green]
> > D Rolfe <dwrolfeFRUITBA T@orindasoft.co m> wrote in message news:<40E2E63F. 9090805@orindas oft.com>...
> >[color=darkred]
> >>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[/color]
> >
> >
> > 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(sysd ate, -1) would give you the
> > first day of the prior month.
> >[/color]
>
> 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[/color]

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,'Y YYYMMDD 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,'M ON') =
to_char(trunc(a dd_months(sysda te,-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(trun c(sysdate), -1), i.e., first of prior month.

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

TO_CHAR(ADD_MON TH 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 --

[Joe Powell]

Re: I must be missing the obvious

I appreciate your responses. I've used the following (I believe
similar) clauses:

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

Or (after altering the nls_date_format )

WHERE datestamp BETWEEN LAST_DAY(ADD_MO NTHS(sysdate,-2))+1
AND LAST_DAY(ADD_MO NTHS(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)f rom 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.

[Wit Serdakovskij]

Re: I must be missing the obvious

Hello, Joe,

[...]
[color=blue]
> 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?[/color]

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.

[Jim Kennedy]

Re: I must be missing the obvious


"Wit Serdakovskij" <wit_no@spam_db a.kiev.ua> wrote in message
news:2004070210 4526.GA26790@to rmoz.net...[color=blue]
> Hello, Joe,
>
> [...]
>[color=green]
> > 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?[/color]
>
> 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[/color]
records[color=blue]
> with datestamp equal to '01-JUN-2004 00:00:00' (not with 01-JUN-04 with[/color]
some[color=blue]
> 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.[/color]

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

[Wit Serdakovskij]

Re: I must be missing the obvious

[...]
[color=blue][color=green][color=darkred]
> > > 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?[/color]
> >
> > 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.[/color][/color]

[...]
[color=blue]
> 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.[/color]

Agreed. That's just a notation from replied message...


--
wbr,
Wit.

[Joe Powell]

Re: I must be missing the obvious

I appreciate your responses. I've used the following (I believe
similar) clauses:

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

Or (after altering the nls_date_format )

WHERE datestamp BETWEEN LAST_DAY(ADD_MO NTHS(sysdate,-2))+1
AND LAST_DAY(ADD_MO NTHS(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)f rom 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.

[Wit Serdakovskij]

Re: I must be missing the obvious

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.

[Jim Kennedy]

Re: I must be missing the obvious


"Wit Serdakovskij" <wit_no@spam_db a.kiev.uawrote in message
news:2004070210 4526.GA26790@to rmoz.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

[Wit Serdakovskij]

Re: I must be missing the obvious

[...]

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.