By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Selecting A Time Range

P: 1
The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.

Please point me in the right direction.

Many thanks
Jan 1 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,186
The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.

Please point me in the right direction.

Many thanks
Your WHERE clause should look something like :
Expand|Select|Wrap|Line Numbers
  1. WHERE Format([YourDate],'HH:nn')<'11:00'
Jan 2 '07 #2

Expert 5K+
P: 8,435
The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.
What is the data type of your time field? If it's Text, then you should be able to do something like
Expand|Select|Wrap|Line Numbers
  1. WHERE [YourTime] < '11:00:00'
If it's date/time, then... um... what about
Expand|Select|Wrap|Line Numbers
  1. WHERE [YourTime] < #11:00:00#
There are a couple of other things to consider:
  • Can you show us what "basic syntax" you have tried?
  • When you say you have "separated" the time value, are you referring to a query which reformats it on the fly? Or a new field you added to the table and copied the time-only values into? I am assuming the latter (new field), so anything I say here may be completely incorrect if that's not the case.
  • The code NeoPa provided will save you the extra storage required for the time-only field, by working directly on the values in the original date/time field. However, since you referred to a "large" database, it might incur a significant processing overhead. If the performance is acceptable (and you don't expect the database to grow very much), then you might as well remove the time-only field and save the disk space. Otherwise, try my technique on the time-only field and you should (fingers crossed...) see better performance...
Jan 2 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
In this particular case
Expand|Select|Wrap|Line Numbers
  1. WHERE Hour([YourDate])<11
would also work.
See Killer's notes above, but I would think creating the extra field would negate any performance benefits associated with processing a field natively rather than calling functions.
Jan 2 '07 #4

Expert 5K+
P: 8,435
...See Killer's notes above, but I would think creating the extra field would negate any performance benefits associated with processing a field natively rather than calling functions.
Surely you jest, sir. 8P

Let's say there are 10 million records. We copy the time value to a new field, and just for fun, let's index it.

So now we want to find records with a particular time value - say, 11:00 AM. Using the new field, we simply hit the index and retrieve the matching records. Using the function, we retrieve every one of those 10,000,000 records, perform a function on each, then throw it away if it didn't match.

Sometimes a little redundancy can make a big impact. It depends on the data, of course.
Jan 2 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
No Killer - I jest not.
Let us assume that the OP has 10,000,000 records and wants to take up the space of a separate index for this one task.
The time taken to create or repopulate an index (You're surely not assuming that this data MUST stay static) is not offset by the time taken to run the embedded function.
Apart from that, how would 'Hitting the Index' return the records you need. Have you run a separate query in advance to create the Date/time field with the date part missing? This creates even more time that needs to be saved before the process can produce a positive time save.
I can accept that there may be circumstances where this can produce an overall time saving, but not that they are likely to occur much, or that the extra complication of the maintenance of the data would be worth that time in any but the most extreme of circumstances (Yours for instance :)).
Jan 2 '07 #6

Expert 5K+
P: 8,435
No Killer - I jest not.
Let us assume that the OP has 10,000,000 records and wants to take up the space of a separate index for this one task.
The time taken to create or repopulate an index (You're surely not assuming that this data MUST stay static) is not offset by the time taken to run the embedded function.
Apart from that, how would 'Hitting the Index' return the records you need. Have you run a separate query in advance to create the Date/time field with the date part missing? This creates even more time that needs to be saved before the process can produce a positive time save.
I can accept that there may be circumstances where this can produce an overall time saving, but not that they are likely to occur much, or that the extra complication of the maintenance of the data would be worth that time in any but the most extreme of circumstances (Yours for instance :)).
Ah, so you're including the time taken to populate the new field, and so on.

Anyway, having been in similar situations, I've found it well worthwhile to add a redundant field and populate it (and index it). Granted the "overall" time might include hours of processing to initially populate the new field, and a slight impact on updates/adds, reducing each user query from hours to seconds is a worthy goal.

I don't agree at all with the need to offset the setup time to produce a "positive time save". Setup time doesn't necessarily directly affect the end-user. A query which takes an hour each time, most definitely does.
Jan 2 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Killer,
I know you have an extreme situation with your database but don't assume that's the same for everybody. Most of my work, for instance, doesn't require that much attention to performance details.
Not many Access users or designers have queries that run for that long - if they did they should be looking elsewhere.
Jan 2 '07 #8

Expert 5K+
P: 8,435
Killer,
I know you have an extreme situation with your database but don't assume that's the same for everybody. Most of my work, for instance, doesn't require that much attention to performance details.
Not many Access users or designers have queries that run for that long - if they did they should be looking elsewhere.
Fair enough, I suppose.
Jan 2 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.