Select only records around a certain time

Question

In my database, there's a record every single minute. I want to only select every record that's created around every fifteen minutes.

The following code shows how I currently execute this query, but with the statement: v.CreateTime > v.CreateTime < endDate), I'm only declaring between what dates I would like to get the data.

I want the data with times of :15, :30, :45, :00. Also the data in not entered in precisely at those times. For example 12:15:23 is possible, but 12:14.43 is also possible.

vafDataList = service.LoadVesselRecord<VafData>(v => 
      v.TypeNo == RecordObjectTypes.VafData && 
      v.Vessel_No == vesselNo && 
      v.CreateTime > startDate && 
      v.CreateTime < endDate)
  .OrderBy(t => t.CreateTime)
  .ToList();

How can I select only the records around the quarters of an hour, and not all records?

That's _very_ trivial, and the code you have will work, if you know _which_ 15 minutes you want. What have you tried? What is your question? What does _"only load a record every 15 minutes one is in there"_ mean? Are you asking for a timer, or for `DateTime.Now.AddMinutes(-15)`? — CodeCaster, Feb 12 '16 at 10:02
Do you mean you want to [truncate the seconds](http://stackoverflow.com/questions/1004698/how-to-truncate-milliseconds-off-of-a-net-datetime) from the result? — CodeCaster, Feb 12 '16 at 10:07
@CodeCaster No, I just want 4 records an hour instead of 60.. And the records should be the ones around `.15, .30, .45, .00` — peer, Feb 12 '16 at 10:08
I don't understand your question... what's the problem? Do you want to round the minutes of your datetimes? — Piero Alberto, Feb 12 '16 at 10:12
What is the maximum deviation, one minute? So you'll never have to select records created at minute 13 or 17? — CodeCaster, Feb 12 '16 at 10:19

score 2 · Accepted Answer · answered Feb 12 '16 at 10:14

2

You can simply select the records for those minutes:

.Where(t => new int[] { 0, 15, 30, 45 }.Contains(t.CreateTime.Minute))

However, assuming your LoadVesselRecord() calls a Where() that gets executed on a DbSet, Entity Framework can't translate that to a query, so you'll have to use the SqlFunctions extensions to let Entity Framework translate it to a DATEPART SQL statement:

.Where(t => new int[] { 0, 15, 30, 45 }
            .Contains(SqlFunctions.DatePart("minute", t.CreateTime)))

answered Feb 12 '16 at 10:14

CodeCaster

147,647
23
218
272

Going to try this out right away! Getting a little error on the: `new int[] { 0, 15, 30, 45 }`. Can't be converted to IENumerable. So I guess I just add `.ToList()` after that, right? – peer Feb 12 '16 at 10:21
You're showing an incomplete message. I suspect you'll need to cast the DatePart to `(int)`. – CodeCaster Feb 12 '16 at 10:28
Getting this error now.. `Additional information: This function can only be invoked from LINQ to Entities.` The where I ended up with giving no errors: `.Where(f => new int[] { 0, 15, 30, 45 }.Contains(Convert.ToInt32(SqlFunctions.DatePart("minute", f.CreateTime))))` – peer Feb 12 '16 at 10:37

Select only records around a certain time

1 Answers1