c# - Find the largest interval between successive timestamps

Yesterday we had a network outage issue and some of the servers were down for a while.So today when I got to work, I wanted to check how bad the effect was on a few systems that I maintain.These systems record/collect data all year long so I wanted to see if there were any gaps between two successive records.

I wrote a small c# program to load all the timestamps and compare them to get the largest gap between timestamps.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace LargestGapInTimeStamp
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                List<DateTime> TimeStampList = new List<DateTime>();
                DateTime oTimeStamp1 = new DateTime();
                DateTime oTimeStamp2 = new DateTime();
                double dMaxSeconds = 0;
                using (SqlConnection oConn = new SqlConnection("server=myserver;database=mydatabase;uid='myid';pwd='mypassword';"))
                {
                    oConn.Open();
                    SqlCommand oCmd = new SqlCommand("select event_time from dbo.mytable with (nolock) where event_time > \'2014-10-01\' and event_time < \'2014-10-09\' order by event_time", oConn);
                    oCmd.CommandTimeout = 30000;
                    SqlDataReader oReader = oCmd.ExecuteReader();
                    while (oReader != null && oReader.Read())
                    {
                        object oTimeStamp = oReader.GetValue(0);
                        if(oTimeStamp != null)
                            TimeStampList.Add(Convert.ToDateTime(oTimeStamp));
                    }

                    for(int iIndex = 0;iIndex < TimeStampList.Count -1 ; iIndex++)
                    {
                        if(dMaxSeconds < (TimeStampList[iIndex + 1] - TimeStampList[iIndex]).TotalSeconds)
                        {
                            dMaxSeconds = (TimeStampList[iIndex + 1] - TimeStampList[iIndex]).TotalSeconds;
                            oTimeStamp1 = TimeStampList[iIndex + 1];
                            oTimeStamp2 = TimeStampList[iIndex];
                        }
                    }
                }

                Console.WriteLine(oTimeStamp1.ToString());
                Console.WriteLine(oTimeStamp2.ToString());
                Console.WriteLine("The largest timestamp gap is: " + dMaxSeconds.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            Console.Read();
        }
    }
}

Output

Extending this further I wanted to list out all the timestamp gaps in the last 8 days since the beginning of the month to see if there were any similar outages.Below program lists all timestamp gaps which are greater than a second.

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

namespace LargestGapInTimeStamp

{

    class Program

    {

        static void Main(string[] args)

        {

            try

            {

                List<TimeStampGap> TimeStampGapList = new List<TimeStampGap>();

                List<DateTime> TimeStampList = new List<DateTime>();

                DateTime oTimeStamp1 = new DateTime();

                DateTime oTimeStamp2 = new DateTime();

                double dMaxSeconds = 0;

                using (SqlConnection oConn = new SqlConnection("server=myserver;database=mydatabase;uid='myid';pwd='mypassword';"))

                {

                    oConn.Open();

                    SqlCommand oCmd = new SqlCommand("select create_ts from dbo.mytable with (nolock) where create_ts > \'2014-10-01\' and create_ts < \'2014-10-09\' order by create_ts", oConn);

                    oCmd.CommandTimeout = 30000;

                    SqlDataReader oReader = oCmd.ExecuteReader();

                    while (oReader != null && oReader.Read())

                    {

                        object oTimeStamp = oReader.GetValue(0);

                        if(oTimeStamp != null)

                            TimeStampList.Add(Convert.ToDateTime(oTimeStamp));

                    }

                    for(int iIndex = 0;iIndex < TimeStampList.Count -1 ; iIndex++)

                    {

                        if (dMaxSeconds < (TimeStampList[iIndex + 1] - TimeStampList[iIndex]).TotalSeconds && (TimeStampList[iIndex + 1] - TimeStampList[iIndex]).TotalSeconds > 1)

                        {

                            dMaxSeconds = (TimeStampList[iIndex + 1] - TimeStampList[iIndex]).TotalSeconds;

                            oTimeStamp1 = TimeStampList[iIndex + 1];

                            oTimeStamp2 = TimeStampList[iIndex];

                            TimeStampGapList.Add(new TimeStampGap(dMaxSeconds,oTimeStamp1,oTimeStamp2));

                        }

                    }

                }

                foreach(TimeStampGap item in TimeStampGapList)

                {

                    Console.WriteLine(item.FirstTimeStamp.ToString());

                    Console.WriteLine(item.SecondTimeStamp.ToString());

                    Console.WriteLine("Timestamp Gap:" + item.Seconds.ToString());

                    Console.WriteLine("************************************");

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

                Console.WriteLine(ex.StackTrace);

            }

            Console.Read();

        }

    }

    public class TimeStampGap

    {

        double _dSeconds;

        public double Seconds

        {

            get { return _dSeconds; }

            set { _dSeconds = value; }

        }

        DateTime _oFirstTimeStamp = new DateTime();

        public DateTime FirstTimeStamp

        {

            get { return _oFirstTimeStamp; }

            set { _oFirstTimeStamp = value; }

        }

        DateTime _oSecondTimeStamp = new DateTime();

        public DateTime SecondTimeStamp

        {

            get { return _oSecondTimeStamp; }

            set { _oSecondTimeStamp = value; }

        }

        public TimeStampGap(){}

        public TimeStampGap(double p_Seconds, DateTime p_FirstTimeStamp, DateTime p_SecondTimeStamp)

        {

            Seconds = p_Seconds;

            FirstTimeStamp = p_FirstTimeStamp;

            SecondTimeStamp = p_SecondTimeStamp;

        }

    }

}

Output

Output confirms that yesterday's outage was significant ! I'll try to post a sql solution soon.