CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworklinq
Shan k
Shan k

Reputation: 199

Select specific time range on each day

I am having a stock market database in which price of the commodity is stored every 5 mins. Table is having below columns

Since, stock exchange closes from

So, I need to exclude these results from my select query.

Is there any way to exclude data for each

Friday 22.00 GMT to Sunday 22.00 GMT

in query.

Since, the database size is very large, so I am avoiding to use Cursor or Loops. I am using LINQ in the application and trying to find out some tricky SQL hack, and make a Select query for it.

Upvotes: 1

Views: 1331

Answers (4)

Ivan Stoev
Ivan Stoev

Reputation: 205569

Here is the LINQ to Entities solution based on this post and Linq query DateTime.Date.DayOfWeek thread:

var query = from stock in db.Stocks
            let firstSunday = new DateTime(1753, 1, 7)
            let dayOfWeek = (DayOfWeek)(DbFunctions.DiffDays(firstSunday, stock.Time).Value % 7)
            let hour = stock.Time.Hour
            where !((dayOfWeek == DayOfWeek.Friday && hour >= 22) ||
                    (dayOfWeek == DayOfWeek.Saturday) ||
                    (dayOfWeek == DayOfWeek.Sunday && hour < 22))
            select stock;

Upvotes: 0

Ian
Ian

Reputation: 30813

If you use LINQ in C# and assuming that your Time is of DateTime struct type and in the GMT (or UTC) time zone, then in the DataTable collection items, you could filter by using Where clause for the given limitation:

var query = stocks
    .Where(x => x.Time.DayOfWeek != DayOfWeek.Saturday //not Saturday
    || !(x.Time.DayOfWeek != DayOfWeek.Friday && x.Time.Hour >= 22) //not Friday after 10PM
    || !(x.Time.DayOfWeek != DayOfWeek.Sunday && x.Time.Hour <= 22)); //not Sunday before 10PM

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

Time is of DateTime(2) type I guess. You could have a criteria such as:

SELECT  *
FROM    [MyTable]
WHERE   ( ( DATEPART(dw, [Time]) + @@DATEFIRST + 6 ) % 7 <> 6
          AND ( ( DATEPART(dw, [Time]) + @@DATEFIRST + 6 ) % 7 <> 5
                OR DATEPART(HOUR, [Time]) < 22
              )
          AND ( ( DATEPART(dw, [Time]) + @@DATEFIRST + 6 ) % 7 <> 0
                OR CAST([Time] AS TIME) >= '22:00'
              )
        );

With Linq To SQL (Linq To EF have special DbFunc for datetime):

var ts = TimeSpan.FromHours(22);
var data = db.MyTable
    .Where(t => t.Time.DayOfWeek != DayOfWeek.Saturday &&
    (t.Time.DayOfWeek != DayOfWeek.Friday || t.Time.TimeOfDay < ts) &&
    (t.Time.DayOfWeek != DayOfWeek.Sunday || t.Time.TimeOfDay >= ts));

This would create a similar (but not same) SQL.

Upvotes: 0

Sergej Muromcev
Sergej Muromcev

Reputation: 21

There's no trick needed depending on database type and "Time" column data (if it's actually datatime, not time-only)

You can use SQl datepart command to pick specific weekday and hour parts, so you could include that into WHERE clause

Why not LINQ solution - creation of view might be beneficial for database performance in case of

  • correctly settled up indexed

  • if database can improve view execution plan automatically

  • if you're using materialized views (extreme boost if using it right with cost of some storage space)

Upvotes: 1

Related Questions