OraFAQ Forum: SQL & PL/SQL » First datetime in a block

Home » SQL & PL/SQL » SQL & PL/SQL » First datetime in a block

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

First datetime in a block [message #286423]

Fri, 07 December 2007 06:01

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

any example code would be greatly appreciated.

Any test case is greatly appreciated.

Yes, it is possible.

Regards
Michel

Report message to a moderator

Re: First datetime in a block [message #286428 is a reply to message #286423]

Fri, 07 December 2007 06:11

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

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.

Report message to a moderator

Re: First datetime in a block [message #286441 is a reply to message #286438]

Fri, 07 December 2007 07:04

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

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...

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: First datetime in a block [message #286501 is a reply to message #286474]

Fri, 07 December 2007 11:33

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Alternatively, cut all that nonsense out and use the LAG function Smile

A quick example:

Code: [Select all] [Show/ hide]

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

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

Littlefoot
Messages: 21824
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.

Report message to a moderator

Re: First datetime in a block [message #286792 is a reply to message #286777]

Mon, 10 December 2007 02:34

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: First datetime in a block [message #286944 is a reply to message #286777]

Mon, 10 December 2007 10:46

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 06:25:02 CDT 2025