difference of date/time field

[khawarameer]

Dear All,

I have a table of 20000 records. Records have a datetime field. I want to select all records where gap between one record and subsequent record is more than one hour [condition to be applied on datetime field].

can any one please help.

regards

KAM

 

[mole999]

if they are sequential, then in a spare column, take one row from the next, and format as time [h]:mm then you should be able to filter on that

 

[khawarameer]

if they are sequential, then in a spare column, take one row from the next, and format as time [h]:mm then you should be able to filter on that

Hi,

Thanks for the response. Actually i needed SQL command if you can provide that i will be grateful.

regards

 

[mole999]

I have no idea how to process that sql, guessing you would need a temp table, and there is no guarantee that the data is stored normalised in a data set

 

[Joe4]

These type of things are not the easiest to do in Access/SQL. That is because the order of records in relational database tables really don't have much meaning. Someone once gave the great example of saying "think of a database table as a bag of marbles". In this way, it is very different than Excel.

Normally, if I had a problem like this, I would probably sort the records and then use VBA and Recordsets to loop through each record.

You may want to take a look at this thread here, which references some options: Compare current to previous record in query - Access World Forums

 

[Micron]

Kam, as you've probably figured out from that very informative thread, it's only possible with vba. I see the solution as a pair of recordsets; one based on a sorting query and the other on a target table where one or two pieces of information could be placed (e.g. RecID) to link the target to the main table records and retrieve from the main table those records whose ID's are in the target table.

A programmer would need to know
- the name of the query that will provide the sort order. This query must be sorted (i.e. have an ORDER BY clause on 1 field)
- name of a table where *at most, 2 fields from this query can be written for both records IF the time difference between two records is greater than 1 hour.
- the 2 target table field names and 2 query source field names have to be known.
- the name of the source query field whose dates are being compared
That's all I can think of for now. As for *, I'm saying that if I were to help with this, 2 fields is the most I'd want to have to deal with. If you cannot relate the table created records with the original table (source) with 2 fields, there may be an issue of poor normalization. In the best scenario, you should be able to make that link on one ID field.

 

[Micron]

As an afterthought, hope you realize that if after sorting by date that two concurrent records have no relative meaning, then the whole exercise is probably pointless. That is to say that if record 1 start time has something to do with Frank but record 2 end time has something to do with Tom, I suspect the comparison is useless. Your sort query would have to prevent meaningless organization of records based on one Date/Time field.

 

[xenou]

I have a table of 20000 records. Records have a datetime field. I want to select all records where gap between one record and subsequent record is more than one hour [condition to be applied on datetime field].

An example, from a simple mock up table with two fields, ID (autonumber) and MyField (DateTime):

select *
from
	Table1 t1
	where (select min(t2.MyField) from Table1 t2 where t2.MyField > t1.MyField)-t1.MyField > (1/24)

The key is to use the correlated subquery to get the next record in sequence (datewise) and then check the time difference between them.

----------
By definition the last record will be omitted from the results - but also there is not a one-hour gap between it and the next record in sequence (datewise) since that record doesn't exist. Also, for a small table like this performance should not be an issue but indexing the date field would probably help if the query seems to be slow.

 

[Micron]

As the linked page in post 5 pointed out, what you see in a table is not necessarily the order of the records. I find that an interesting concept and have to wonder why not, even when a sort order is imposed on the table. Based on that thread, a query to organize the records was required first as the only way to ensure a set of records is organized in the desired order. I'm intrigued by your solution because of the syntax (i.e. there's no explicit construct for the aliases you use, which I thought was a requirement), so I know who to ask if I'm struggling with a sub query. You and a couple of others at Access Forums are masters at it!

Be that as it may, I'm curious to know your thoughts on how reliable the results can be, given the seemingly popular notion about the order of records in a table. Did you read the thread at the link? The notion that a set of table records is like a bag of marbles is interesting ("you never know what you're gonna get.") My apologies to Forrest!

 

[xenou]

Hi,

Correlated queries work here because correlated queries run once for each row in the outer dataset. So, for each record, find the one with the next highest date, and then check if it is less than 1 hour in the future. SQL will repeat that process for each row, so you can check each record, though not necessarily "in order" - all you can be sure is that all of them will be checked. Aliasing the outer table Table1 as t1 is essentially so it can be used in the inner query to reference a field value in the outer query.

I read the linked page quickly. I agree with the general statement that tables should never be assumed to have order. However, I don't agree that you have to use vba or cursors to find records that are "next" or "previous". Just like any numbers, you can use sql comparative operators with min() or max() to find them, even if they are not in order (the smallest number that is bigger than this one is "next"). It might be overkill to say SQL has no concept of next or previous - I find it a little too black and white. Next is a semantic concept that can be defined as part of your data, and if SQL needs to handle it, it can. We probably do it more often than we realize - a payment will be applied to the oldest outstanding invoice, then the "next" one, and so on. A student waiting list will assign open seats to the first student on the waiting list, then the "next" one. You may in practice choose to implement this with code or even with cursors, but nothing necessarily prevents you from using SQL if you want to.

I wouldn't object to using cursors or vba, by the way. It would be worth testing to see if the extra overhead creating cursors objects pays off with faster processing or not. An indexed data field should allow for faster searching using binary search techniques so it shouldn't be terribly slow but correlated queries in general are going to perform worse than most queries due to the nature of the beast. In this particular case, ordering the records and then going one by one (in order ) may work efficiently if speed is a concern.

I don't know about the aliasing - if you mean I didn't use the keyword AS, then yes that is optional. I believe in the subquery the t2 alias is also option (the table could be unaliased) - that's how I'd do it in SQL Server. But I would need to test it to be sure in MSAccess:

where (select min(t2.MyField) from Table1 t2 where t2.MyField > t1.MyField)

Or:

where (select min(MyField) from Table1 where MyField > t1.MyField)