473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 5069


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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
102
by: Skybuck Flying | last post by:
Sometime ago on the comp.lang.c, I saw a teacher's post asking why C compilers produce so many error messages as soon as a closing bracket is missing. The response was simply because the compiler...
1
by: Ctal | last post by:
Relatively new to dotnet so bear with me if this is obvious. In the course of my career, I've assembled a collection of icons to use with my apps. Most of these are 32x32, 256 or 16x16, 16. ...
1
by: Dominick Baier | last post by:
Hi, you are right - Windows needs the password in plaintext to impersonate a user (having to call LogonUser, which requires a password). Thinking about it - it is the only way Windows can do it. ...
0
by: JohnR | last post by:
Hi all.. my application uses a custom dll which should be in the same directory as the exe file. If the dll file is missing the application immediately aborts with a "Application has generated an...
2
by: Steven D'Aprano | last post by:
When using the timeit module, you pass the code you want to time as strings: import timeit t = timeit.Timer("foo(x, y)", \ """from module import foo x = 27 y = 45 """) elapsed_time =...
77
by: Ville Vainio | last post by:
I tried to clear a list today (which I do rather rarely, considering that just doing l = works most of the time) and was shocked, SHOCKED to notice that there is no clear() method. Dicts have it,...
1
by: BobPaul | last post by:
I'm following code out of a howto book and this is really bugging me. This header file was created by VStudio 6.0 when I did a "Right Click: Add Member Function" CLine is a class I wrote (per the...
8
by: =?Utf-8?B?cDNqb2hu?= | last post by:
Hi all, Not sure if this is the correct newsgroup for this, but I'm in a jamb and need some input. I came across a problem that I think is related to the C# compiler in Visual Studio 2008. The...
9
by: Boltar | last post by:
On 32 bit linux with gcc 4.2 I get unexpected results with this code: main() { int bits = 32; printf("%d\n",(int)1 << (int)32); printf("%d\n",(int)1 << bits); }
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.