Display Query results based on last 24 hours 5

[nicks60610]

I am trying to query my database to show me data that was entered within the last 24 hours; this is what I have in the query, but I know it's not right because it errors out each time...

TO_DATE(EQ_EVENTS.EVENT_END_DATE, 'MM/DD/YY') = TO_DATE(SYSDATE-24, 'MM/DD/YY':hh24:Mi:ss)

Can anyone please show me in the right direction so I can learn more about this function and complete this query? I have been looking online, but I cannot find much regarding what I am trying to accomplish.

Thanks,

 

[SantaMufasa]

Nick,

First, if a column is already of type DATE (as I presume is the case with EQ_EVENTS.EVENT_END_DATE, and is certainly the case with SYSDATE), then you do not use the TO_DATE function. Given those circumstances, here is a code fragment to replace the one you posted, above:

...WHERE EQ_EVENTS.EVENT_END_DATE > SYSDATE-1...

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

if I use SYSDATE-2 I see data entries from 3/27 and 3/26; however if I use -1, it shows me until 00:01 (Midnight) of 3/27 and forward...

Is this by design, so if I want for the last 24 hours, I would use -2?

Thanks SantaMufasa!

 

[SantaMufasa]

I'm sorry...I didn't know that you had events for which their EQ_EVENTS.EVENT_END_DATE surpassed the current time (SYSDATE). Therefore, if you want just events for which their end times are literally between the current moment and exactly 24 hours ago, then this is the code you want:

...WHERE EQ_EVENTS.EVENT_END_DATE
         BETWEEN SYSDATE-1 and SYSDATE...

If you say "SYSDATE-2", that goes back exactly 48 hours (2 days) from the current moment.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

When I put in EQ_EVENTS.EVENT_END_DATE BETWEEN SYSDATE-1 and SYSDATE-3 (as an example), I see no data? I can use

EQ_EVENTS.EVENT_END_DATE > SYSDATE-1 and it shows me from right now going back to 00:01 this morning; and when I use >SYSDATE-2, I see data from 21:35 last night...unfortunately do I not have a good frame of reference as of yet as no data was entered over the weekend...but by tomorrow I should see what >SYSDATE-2 gives me...

 

[Turkbear]

Hi,
Use
EQ_EVENTS.EVENT_END_DATE BETWEEN SYSDATE-3 and SYSDATE-1

The range operator ( the Between ) translates to

where
EQ_EVENTS.EVENT_END_DATE >= <First Value> and
EQ_EVENTS.EVENT_END_DATE <= <Second value>

so your formula was asking for all data
>= Yesterday and <= 3 days ago - that will not return any data.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nicks60610]

Cool - I did this:

EQ_EVENTS.EVENT_END_DATE BETWEEN SYSDATE-2 and SYSDATE-0

This gives me the most recent data entry for today through yesterday morning, so basically it gives me something like 36 hours (I wonder if it is not using the system time as entered on the server and rather using GMT?)

Thanks guys!!!

 

[SantaMufasa]

Nick,

First, you never need to use the syntax "-0" on anything mathematical in Oracle.

Second, as I explained earlier, SYSDATE's value is the currect date/time, accurate to the second. "SYSDATE-1" yields a date and time that is exactly (to the second) 24:00:00 hours (i.e., 1 day) ago. If you use "SYSDATE-2", then that gives you precisely 48:00:00 hours prior to the current moment in SYSDATE (i.e., 2 days ago). The fact that using "betwee SYSDATE-2 and SYSDATE" gives you data that is 36 hours old simply means that that you have no data in your database that is between 48 hours and 35:59:59 hours old.

I am trying to query my database to show me data that was entered within the last 24 hours

Unless your needs have changed, the code that I posted at 27 Mar 06 15:13, above, is precisely what you need, despite your possibly not having test data that fits the 24:00:00-to-present time frame.

(BTW, thanks for the Purple Star.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

But if I use SYSDATA-1 all I see is data from 00:17 AM (3/27) until 14:51 (3/27), and if I look in the database for that department, there is data entered as early as 06:46 AM (3/26), so SYSDATA-1 does not go back 24 hours, which I know does not make any logical sense...

Let me try again using SYSDATA-1 in the query and see what it yields, I did make a change to the sort (could be what messed it up before..)

 

[nicks60610]

I think I see the problem, there is a Date field and a Time field, and what I see is all data from 3/27-3/27 and the Time goes from 00:17 - 15:11, I wonder if the problem is not within the DB, something is not being calculated properly when a new record is added to the database....

very bizarre indeed...

 

[SantaMufasa]

Nick, my buddy, there is no place on earth right now that is earlier than 06:46 on March 27, 2006. Where you are right now, I'm guessing that it is, in fact, about 16:10 on March 27, 2006. Therefore, if you take the current value of SYSDATE where you are and subtract 24:00:00, then that is March 26, 2006 @ 16:10...06:46 on March 26, 2006, is certainly not between March 26, 2006 @ 16:10 and the current moment on the east coast of the US, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

Correct, which doesn't make any sense, It is 16:10 March 27 East coast time; so I should only see the entries from 15:11 back to 16:38 3/26, but I don't :- (, instead EQ_EVENTS.EVENT_END_DATE > SYSDATE-1 shows me this:

00:17
1:45
2:40
2:52
3:24
3:25
3:36
3:36
3:40
3:42
3:44
3:45
3:47
3:53
4:09
5:26
5:47
6:53
7:56
7:57
8:35
9:02
9:05
12:07
12:08
12:09
12:11
12:37
12:38
12:39
12:58
14:03
14:46
14:50

Of course there is more data, but that's the time stamp and the corresponding date stamp for all those entries are 3/27/06....

I know it should give me -24 Hours, but it's not :-( so what I am willing to do is use > SYSDATE-2, and it does give me more than 24, but less than 48 (again, not logical, but it does..)

Thanks for your help with this SantaMufasa, if I could show you the DB I would, but it is internal only unfortunately...

 

[SantaMufasa]

Nick,

Is there any method by which you can enter a dummy event on your application, then inspect the data that it stores and compare the time that it stores with your current clock time? That way we could rule out the timezone effect in this case.

Do you have any empirical evidence that your table contains rows whose values are more recent than 24:00:00 hours ago, yet earlier than the "00:17" (presumably from this morning)? If so, could you please post just date/time values here?

It is too late for this application, but FYI Oracle has a data type called TIMESTAMP, which stores date/time information down to the nanosecond (10^-9), but the important attribute (possibly) in your case, is that it also has a timezone attribute.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

Here is the data from 3/26 - 3/27

EVENT_END_DATE EVENT_END_TIME
3/26/2006 06:46
3/26/2006 06:47
3/26/2006 06:48
3/26/2006 16:32
3/26/2006 16:33
3/26/2006 21:33
3/26/2006 21:35
3/27/2006 00:17
3/27/2006 01:45
3/27/2006 02:40
3/27/2006 02:52
3/27/2006 03:24
3/27/2006 03:25
3/27/2006 03:36
3/27/2006 03:36
3/27/2006 03:40
3/27/2006 03:42
3/27/2006 03:44
3/27/2006 03:45
3/27/2006 03:47
3/27/2006 03:53
3/27/2006 04:09
3/27/2006 05:26
3/27/2006 05:47
3/27/2006 06:03
3/27/2006 06:53
3/27/2006 07:56
3/27/2006 07:57
3/27/2006 08:13
3/27/2006 08:35
3/27/2006 08:51
3/27/2006 08:52
3/27/2006 08:53
3/27/2006 08:58
3/27/2006 08:59
3/27/2006 09:02
3/27/2006 09:05
3/27/2006 10:07
3/27/2006 11:23
3/27/2006 11:24
3/27/2006 11:26
3/27/2006 11:28
3/27/2006 12:07
3/27/2006 12:08
3/27/2006 12:09
3/27/2006 12:11
3/27/2006 12:37
3/27/2006 12:38
3/27/2006 12:39
3/27/2006 12:58
3/27/2006 13:03
3/27/2006 13:04
3/27/2006 13:57
3/27/2006 14:02
3/27/2006 14:03
3/27/2006 14:21
3/27/2006 14:46
3/27/2006 14:47
3/27/2006 14:50
3/27/2006 15:11
3/27/2006 18:20
3/27/2006 18:21
3/27/2006 18:21
3/27/2006 18:22
3/27/2006 18:22
3/27/2006 18:23
3/27/2006 18:23
3/27/2006 18:23
3/27/2006 21:57
3/27/2006 21:58
3/27/2006 21:58
3/27/2006 21:59
3/27/2006 22:34
3/27/2006 22:46
3/27/2006 22:47

 

[ddiamond]

Nick,

Does Event_End_Date contain just a date, or does it contain date and time? From your sample data, it looks like you are storing the time in a seperate field. If that is the case, I can understand why you are getting strange results.

- Dan

 

[nicks60610]

Yes, the date is stored in Field A and the time in Field B; unfortunately was this not my database design; it is a commercial database we purchased and we're trying to generate better reports on our own using .NET, but like this example, it shows us how the data can sometimes skew the queries...

 

[ddiamond]

In that case, you have to convert it into date-time in your where clause:

...WHERE to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || 
         to_char(EVENT_END_TIME, 'HH24:MI'),'MM/DD/YYYY HH24:MI')
         BETWEEN SYSDATE-1 and SYSDATE...

My mothod of combining the date and time is a bit ugly. If anyone has a more elegant method, please post.

 

[SantaMufasa]

DD, your code is not ugly...the database design (for which Nick is not responsible)is ugly. Your way is the simplest and most straightforward method for resolving Nick's need. I'm just sorry that my realisation that his DATE and TIME components were distinct from one another came so late in the game.

Nick, since every Oracle DATE column also, by definition and without exception, contains storage components for TIME, if there is an opportunity to have a "Come to Jesus" talk with your third-party-application developers, you should rattle their cages about the avoidable grief that results from their half-baked, unprofessional design of their DATE/TIME components. (Climbing down now from my soapbox.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nicks60610]

Mufasa, you have no idea how sad their design is, there are so many violations of DB logic in this database, one of which is updating Table 3 based on the millisecond timestamp from Table 2, and this separation of the Date/Time, there are so many others, more than I could mention here...thanks to all of you for trying to help me sort this mess out; it is a pain to work with, but what don't you do to please your customers?

 

[nicks60610]

ddiamond, I tried what you posted and it didn't like it :-(, it complained about a non-numeric value in numeric field...I assume it did not like the time value or something like that...

This is the error:

ORA-01722: invalid number

WHERE to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' ||to_char(EVENT_END_TIME, 'HH24:MI'),'MM/DD/YYYY HH24:MI')
BETWEEN SYSDATE-1 and SYSDATE-2