Re: Oracle SQL Help

"ext237" <ext237_at_somewhere.com> wrote in message news:q8ku30donkig4nllbn8oo6e5mbilq3egbr_at_4ax.com...
|
| Hi
|
| Whats the SQL syntax to gather records based on a date-time filed,
| where the field's value is within the last 60 minutes?
|
| I can get the info based on 12am today using this statement -- but the
| last 60 minutes is far more helpful.
|
| SELECT FIELD_1, COUNT(FIELD_1)
| FROM TABLE_1
| WHERE FIELD_2 = 1345
| AND TO_CHAR(CREATED_DTM,'MM/DD/YYYY') = TO_CHAR(SYSDATE, 'MM/DD/YYYY')
| GROUP BY FIELD_1
|
| Any suggestions?
|
| THANKS!
|
|

date arithmetic is in days

sysdate is the current system date (on the database machine, not the client or app server)

so, 60 minutes ago is: (sysdate - (1/24))

use this expression in your predicate and avoid using a function on the actual column (general performance guideline, in case the column is indexed)

AND CREATED_DTM >= (sysdate - (1/24))

[ my apologies to purists for spool-feeding this one, but date handling is taught incorrectly in most SQL intro classes, so i feel strongly that a complete example is warranted ]

;-{ mcs Received on Fri Feb 27 2004 - 08:44:24 CST