Home » SQL & PL/SQL » SQL & PL/SQL » First datetime in a block
icon5.gif  First datetime in a block [message #286423] Fri, 07 December 2007 06:01 Go to next message
VentureFree
Messages: 3
Registered: December 2007
Junior Member
I've worked a pretty complicated problem down to this simpler one, which will help me unlock the more difficult one if I can figure it out.

Say I have a list of events, each timestamped with a datetime. Now, given an arbitrary datetime, I need to able to find the first record whose timestamp is less than one hour before the next timestamp in the record set, starting with the given datetime (of course). So, for instance if I had the following data:

'12/03/2007 3:40:11 AM'
'12/03/2007 5:29:13 AM'
'12/03/2007 5:59:11 AM'
'12/03/2007 6:26:01 AM'
'12/03/2007 7:19:38 AM'
'12/03/2007 7:43:48 AM'
'12/03/2007 9:44:17 AM'

And I was given the datetime '12/03/2007 7:00:00 AM', I need a query that will return the single record with the datetime of '12/03/2007 5:29:13 AM' (the second record in the set), since the record before that is more than an hour before it.

Is this even possible? If so, any example code would be greatly appreciated.

[Updated on: Fri, 07 December 2007 06:09]

Report message to a moderator

Re: Block of Activity [message #286427 is a reply to message #286423] Fri, 07 December 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

any example code would be greatly appreciated.

Any test case is greatly appreciated.

Yes, it is possible.

Regards
Michel
Re: First datetime in a block [message #286428 is a reply to message #286423] Fri, 07 December 2007 06:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Your query is not clear as

even '12/03/2007 5:29:13 AM' is more than an hour before '12/03/2007 7:00:00 AM'

Thumbs Up
Rajuvan.

[Updated on: Fri, 07 December 2007 06:13]

Report message to a moderator

Re: First datetime in a block [message #286438 is a reply to message #286428] Fri, 07 December 2007 06:47 Go to previous messageGo to next message
VentureFree
Messages: 3
Registered: December 2007
Junior Member
Sorry about not being clear. In part it's because I'm coming from a programming background, and SQL is obviously different than programming. I'm not looking for the first record that's less than an hour before the given time. I'm looking for the first record that's less than an hour before the next record in the database. If I were programming this, I would loop backward through the recordset (backward through the datetimes, that is) and compare each timestamp with the one before. If the difference were greater than 1 hour, then that record is the one I'm looking for.

See the comments below. The second record is the one I'm looking for because it's only 30 minutes before the next one. The first record is more than an hour before this one, which is why I don't want it.

'12/03/2007 3:40:11 AM' --about 1hr 49min before the next record
'12/03/2007 5:29:13 AM' --about 30min before the next record
'12/03/2007 5:59:11 AM' --about 26min before the next record
'12/03/2007 6:26:01 AM' --about 53min before the next record
'12/03/2007 7:19:38 AM' --about 24min before the next record
'12/03/2007 7:43:48 AM' --about 2hr 1min before the next record
'12/03/2007 9:44:17 AM' --no next record to compare to

I hope this makes it a little more clear. If not, I apologize. My brain is fried because this is actually just a small sub-problem in a much larger, much more complicated problem. I think if I can figure this out, the rest will be much easier.
Re: First datetime in a block [message #286441 is a reply to message #286438] Fri, 07 December 2007 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't unerstand you example.
If you ask '7:00', the next record is '7:19' and the one that is the closest but less than 1 hour before is '5:59' not '5:29'.

Still don't have a valid test case (create table and insert statements).
Still don't know your Oracle version.

Read and follow OraFAQ Forum Guide

Regards
Michel

[Updated on: Fri, 07 December 2007 07:05]

Report message to a moderator

Re: First datetime in a block [message #286474 is a reply to message #286441] Fri, 07 December 2007 10:00 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
What your missing is that there is no order to your data until you specify an order. There is no "next record" unless you specify an order for your query result. It may "look" like the records are in some kind of order, but until you actually specify an order, they can come out ordered differently each time.

Now, let's say you create a query such as...

select my_date_stamp
from   my_event_table
ordered by my_date_stamp asc


you'll get information returned in a specific order that you've defined, and not a random ordering based on what the database spits out.

Then, review how cursors work and how to "fetch" each record, place it into a variable and compare the two. DATE types are actually numbers that you can manipulate mathematically. Review the documentation about the DATE type and how it's used. It will also aid you in understanding how the DATE type is stored within an oracle table and how to retrieve, manipulate and display it.

HTH,
Ron
Re: First datetime in a block [message #286501 is a reply to message #286474] Fri, 07 December 2007 11:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Alternatively, cut all that nonsense out and use the LAG function Smile
A quick example:
SQL> with dat as ( SELECT TO_DATE('12/03/2007 3:40:11 AM','mm/dd/yyyy hh:mi:ss am') t FROM dual UNION ALL
  2                SELECT TO_DATE('12/03/2007 5:29:13 AM','mm/dd/yyyy hh:mi:ss am') FROM dual UNION ALL
  3                SELECT TO_DATE('12/03/2007 5:59:11 AM','mm/dd/yyyy hh:mi:ss am') FROM dual UNION ALL
  4                SELECT TO_DATE('12/03/2007 6:26:01 AM','mm/dd/yyyy hh:mi:ss am') FROM dual UNION ALL
  5                SELECT TO_DATE('12/03/2007 7:19:38 AM','mm/dd/yyyy hh:mi:ss am') FROM dual UNION ALL
  6                SELECT TO_DATE('12/03/2007 7:43:48 AM','mm/dd/yyyy hh:mi:ss am') FROM dual UNION ALL
  7                SELECT TO_DATE('12/03/2007 9:44:17 AM','mm/dd/yyyy hh:mi:ss am') FROM dual )
  8  SELECT t
  9        ,LAG(t,1) OVER(ORDER BY t) previous
 10     ,LEAD(t,1) OVER(ORDER BY t) next
 11  FROM dat
 12  ORDER BY t;

T                      PREVIOUS               NEXT
---------------------- ---------------------- ----------------------
12/03/2007 03:40:11 am                        12/03/2007 05:29:13 am
12/03/2007 05:29:13 am 12/03/2007 03:40:11 am 12/03/2007 05:59:11 am
12/03/2007 05:59:11 am 12/03/2007 05:29:13 am 12/03/2007 06:26:01 am
12/03/2007 06:26:01 am 12/03/2007 05:59:11 am 12/03/2007 07:19:38 am
12/03/2007 07:19:38 am 12/03/2007 06:26:01 am 12/03/2007 07:43:48 am
12/03/2007 07:43:48 am 12/03/2007 07:19:38 am 12/03/2007 09:44:17 am
12/03/2007 09:44:17 am 12/03/2007 07:43:48 am

[Updated on: Fri, 07 December 2007 11:44]

Report message to a moderator

Re: First datetime in a block [message #286777 is a reply to message #286501] Mon, 10 December 2007 01:57 Go to previous messageGo to next message
VentureFree
Messages: 3
Registered: December 2007
Junior Member
DOH! I just realized that this is Oracle (you'd think I'd have noticed before now). Mostly I'm interested in the straight SQL, as in non-Oracle specific code. Please feel free to tell me to go to h*ll for wasting your time. BUT in case you don't mind, I think I know how to describe what I'm trying to do. Let me try this. Say I have the following:

CREATE TABLE Events ( UserID int, Timestamp datetime )

INSERT INTO Events VALUES ( 345, ‘12/03/2007 3:22:25 AM’ )
INSERT INTO Events VALUES ( 280, ‘12/03/2007 3:24:41 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 3:40:11 AM’ )
INSERT INTO Events VALUES ( 280, ‘12/03/2007 4:18:32 AM’ )
INSERT INTO Events VALUES ( 280, ‘12/03/2007 4:56:18 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 5:29:13 AM’ )
INSERT INTO Events VALUES ( 280, ‘12/03/2007 5:34:28 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 5:59:11 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 6:26:01 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 7:19:38 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 7:43:48 AM’ )
INSERT INTO Events VALUES ( 345, ‘12/03/2007 11:44:17 PM’ )


(I put some extraneous info in to make it more realistic) Given this as input (UserID = 345, Timestamp = ‘12/03/2007 7:00:00 AM’), I would like a sql statement that returns the following:

345, ‘12/03/2007 5:29:13 AM’
345, ‘12/03/2007 5:59:11 AM’
345, ‘12/03/2007 6:26:01 AM’
345, ‘12/03/2007 7:19:38 AM’
345, ‘12/03/2007 7:43:48 AM’

You’ll note that each returned datetime is no more than 1 hour different from at least one other record in the set, and at least 1 record is no more than 1 hour different from the input time. I think I know how to check for each being within an hour of at least one other:

SELECT DISTINCT Current.UserID, Current.Timestamp
FROM Events as Current INNER JOIN Events as Previous
ON Current.UserID = Previous.UserID AND DateDiff(’h',Current.Timestamp, Previous.Timestamp) = 0
WHERE Current.UserID = @UserID
AND Current.Timestamp <> Previous.Timestamp
ORDER BY Current.Timestamp DESC;


As is this will essentially list all the events for that user, since every event will be within an hour of at least one other event (though not necessarily in my testcase). So how do I limit it to that block of time for which at least one of the records is within an hour of the given datetime? Is that even possible?

[Updated on: Mon, 10 December 2007 02:01]

Report message to a moderator

Re: First datetime in a block [message #286783 is a reply to message #286777] Mon, 10 December 2007 02:11 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Mostly I'm interested in the straight SQL ...

This ANSI SQL dBforum might be interesting for you.
Re: First datetime in a block [message #286792 is a reply to message #286777] Mon, 10 December 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE Events ( UserID int, Timestamp datetime )
  2  
SQL> INSERT INTO Events VALUES ( 345, ‘12/03/2007 3:22:25 AM’ )
  2  
SQL> CREATE TABLE Events ( UserID int, Timestamp datetime )
  2  /
CREATE TABLE Events ( UserID int, Timestamp datetime )
                                            *
ERROR at line 1:
ORA-00902: invalid datatype


SQL> CREATE TABLE Events ( UserID int, datetime Timestamp );

Table created.

SQL> INSERT INTO Events VALUES ( 345, ‘12/03/2007 3:22:25 AM’ )
  2  /
SP2-0552: Bind variable "22" not declared.


No comment.

Regards
Michel
Re: First datetime in a block [message #286944 is a reply to message #286777] Mon, 10 December 2007 10:46 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I still have NO idea what 7:00 Am has to do with anything.

Previous Topic: Can we find date/dates based on ..... ?
Next Topic: DBMS_SCHEDULER
Goto Forum:
  


Current Time: Mon Dec 05 08:55:11 CST 2016

Total time taken to generate the page: 0.08374 seconds