Select specific time range on each day

Question

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

ID
Commodity
Time

Since, stock exchange closes from

Friday 22.00 GMT to Sunday 22.00 GMT

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.

Sergej Muromcev · Answer 1 · 2016-05-18T08:01:35.800

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)

Ian · Answer 2 · 2016-05-18T09:12:50.550

1

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

edited May 18 '16 at 09:12

answered May 18 '16 at 07:46

Ian

30,182
19
69
107

Hi Ian, I have tried x.Time.DayOfWeek, but this does not works, We needs to use SQLFunctions for the same. LINQ throws an error on x.Time.DayOfWeek, as this does not get converted into sql function datepart. – Shan k May 18 '16 at 08:06
What is the data type on the database for the `Time`? is it datetime? – Ian May 18 '16 at 08:07
OK, and is your solution must use `LINQ`? Do you consider having, say, `View` in your Database instead? If you consider solution in the database level, you could create a `View` in the SQL server with the same `Where` clause concept to get your data table, I think – Ian May 18 '16 at 08:10
With Linq To EF it wouldn't work. With Linq To SQL, expression needs to be corrected, it would be || not && for Friday and Sunday. Otherwise it would filter out all. – Cetin Basoz May 18 '16 at 09:07

Cetin Basoz · Answer 3 · 2016-05-18T09:09:57.363

0

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.

edited May 18 '16 at 09:09

answered May 18 '16 at 08:59

Cetin Basoz

22,495
3
31
39

Hi Cetin, I have tried the t.Time.DayOfWeek, but this is throwing error, saying that 'DayOfWeek' is not supported in LINQ to Entity.... please help – Shan k May 18 '16 at 10:20
But I have already said that this wouldn't work with Linq To EF (you need to workaround using its datetime DbFunctions if you want to use it). The code I gave works with Linq to SQL. And also with EF you could directly use the SQL with .ExecuteQuery( "select ..."). – Cetin Basoz May 18 '16 at 12:38

score 0 · Answer 4 · edited May 23 '17 at 12:07

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;

Select specific time range on each day

4 Answers4