Query for data every hour

Question

I have a bunch of date data in a sql table like this:

2015-01-1 1:00:00, "some data"
2015-01-1 1:25:00, "some data"
2015-01-1 1:40:00, "some data"
2015-01-1 2:00:00, "some data"
2015-01-1 2:30:00, "some data"
2015-01-1 3:30:00, "some data"
            .
            .
            .
2015-01-31 23:00:00, "some data"
2015-01-31 23:50:00, "some data"

I want to select a report which shows the data in month of january(01) every hour and if for example there is no data on this time 1/1/2015 3:30:00 put Null data for example:

2015-01-1 1:00:00, "some data"
2015-01-1 2:00:00, "some data"
2015-01-1 3:0:00, "Null data"
            .
            .
            .
2015-01-31 23:00:00, "some data"

this is my code:

var query = from item in repo.IonogramRepository.GetAll()
                   where month == item.DateTime.Month
                   orderby item.DateTime ascending
                   select new { item .DateTime,item.Data}
                         ;

but as you see this selected all, and I need select data every hour and if don't exist put Null Data. Any idea?

Thanks in advance.

@Ako Thanks for your advice. it was very helpfull. Since you were right, you should have answered the question, not just only with a comment, considering the comment of DrewJordan. I'll answer with the solution that I found with your advice — Cyberguille, Feb 24 '15 at 20:52

score 1 · Accepted Answer · answered Feb 27 '15 at 21:50

This is the solution tha I found with the help of Akont's comment:

  static void Main(string[] args)
    {
         //Example to test, I'm working with a Database
        List<Tuple<DateTime, string>> repo = new List<Tuple<DateTime, string>>(){new Tuple<DateTime, string>(new DateTime(2015,1,1,1,0,0),"Some Data" ),
                                                                                  new Tuple<DateTime, string>(new DateTime(2015,1,1,1,25,0),"Some Data" ),
                                                                                  new Tuple<DateTime, string>(new DateTime(2015,1,1,1,40,0),"Some Data" ),
                                                                                  new Tuple<DateTime, string>(new DateTime(2015,1,1,2,0,0),"Some Data" )};


        var query = from  dateTime in  GenerateStaticListOfDateTime(1,2015)
                    join item in repo on dateTime equals item.Item1 into data
                    from item2 in data.DefaultIfEmpty(new Tuple<DateTime, string>(dateTime,"Null data"))
                    select item2;
        foreach (var item in query)
        {
             Console.WriteLine("DateTime:{0} Data:{1}  ",item.Item1,item.Item2);
        }
    }

    public static List<DateTime> GenerateStaticListOfDateTime(int month,int year)
    {
        var result = new List<DateTime>();
        for (int i = 1; i <= DateTime.DaysInMonth(year,month); i++)
        {
            for (int j = 0; j < 24; j++)
            {
                result.Add(new DateTime(year,month,i,j,0,0));
            }
        }
        return result;
    }

I'm pretty sure that this idea can be explained a little more, but this is what I have until now. Thanks for your help.

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

I'm not really the best one to answer this. But maybe I can be of use. First i believe you have to make a call about whether you want the last entry within the hour or the last. That done I believe you can employ the technique found here

edited May 23 '17 at 10:24

Community

1
1

answered Feb 23 '15 at 21:24

Pedro Braz

2,261
3
25
48

Query for data every hour

2 Answers2