Chart from SQL Datasource but DateTime in Ticks

Question

I have a SQL database that stores some data that I would like to chart. The problem is, I inherited this database and they store the datetime values as Ticks. When I set my chart datasource to this table, it doesn't seem to understand ticks.

How do I get my chart to convert the ticks back to a DateTime format that my chart understands?

Database Table

My SQL query and code:

static public DataTable get_I1(RunningTests rt)
{
    DataTable dt = new DataTable();

    using (SqlConnection cs = new SqlConnection(connString))
    {
        string query = string.Format("SELECT Time_Stamp, I1 FROM Test WHERE Unit_ID = '{0}' AND Time_Stamp >= '{1}' AND Time_Stamp <= '{2}'", rt.Unit_ID, rt.StartTime.Ticks, rt.StopTime.Ticks);
        Console.WriteLine(query);
        SqlCommand cmd = new SqlCommand(query, cs);

        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }

    dt.DefaultView.Sort = "Time_Stamp DESC";
    dt = dt.DefaultView.ToTable();

    return dt;
}

My code to set my chart datasource:

private void do_chart_I1(RunningTests rt)
{
    muCalGUI1.chartI1.Series.Clear();

    DataTable dt = SQL.get_I1(rt);

    muCalGUI1.chartI1.DataSource = dt;

    Series s = new Series("I1");
    s.XValueMember = "Time_Stamp";
    s.YValueMembers = "I1";
    s.ChartType = SeriesChartType.Line;
    s.BorderWidth = 2;
    s.MarkerSize = 5;
    s.MarkerStyle = MarkerStyle.Circle;

    muCalGUI1.chartI1.ChartAreas[0].AxisY.IsStartedFromZero = false;
    muCalGUI1.chartI1.ChartAreas[0].AxisX.LabelStyle.Format = "yyyy-MM-dd\nHH:mm:ss";
    muCalGUI1.chartI1.ChartAreas[0].AxisY.LabelStyle.Format = "0";
    muCalGUI1.chartI1.ChartAreas[0].RecalculateAxesScale();

    muCalGUI1.chartI1.Series.Add(s);
    muCalGUI1.chartI1.Legends.Clear();
}

Results:

Desired Results:

Side note: date/time should usually be queried upper-bound exclusive (`<`), to avoid certain boundary issues. Among other things, this makes it easier to "stride" the dataset - group the results by minute, for example. — Clockwork-Muse, Feb 20 '19 at 23:57
Thanks, I went ahead and changed it in my code. The test can be ran multiple times for the same ID, so I only want to show the test samples during that test time. — Baddack, Feb 21 '19 at 00:02
There shopuld be a DB function to do the conversion, no? Which DB do you use? — TaW, Feb 21 '19 at 08:07

score 0 · Answer 1 · answered Feb 20 '19 at 23:51

I have a solution that works. If someone can provide a 'more clean' approach, I'll be happy to mark that as the answer. For now my work around was to create a new datatable and convert the ticks to a datetime.

SQL Code:

static public DataTable get_I1(RunningTests rt)
{
    DataTable dt = new DataTable();

    using (SqlConnection cs = new SqlConnection(connString))
    {
        //string query = string.Format("Select TOP {0} Serial AS [Serial #], Start, [Stop], N, ROUND(Mean,4) AS Mean, ROUND(StdDev,4) AS [Standard Deviation], ROUND(Minimum,4) AS Min, ROUND(Maximum,4) AS Max FROM TestTime JOIN Membrane ON TestTime.Membrane_ID = Membrane.Membrane_ID WHERE Serial LIKE '{1}' ORDER BY TestTime_ID", numRecords, serial);
        string query = string.Format("SELECT Time_Stamp, I1 FROM Test WHERE Unit_ID = '{0}' AND Time_Stamp >= '{1}' AND Time_Stamp <= '{2}'", rt.Unit_ID, rt.StartTime.Ticks, rt.StopTime.Ticks);
        SqlCommand cmd = new SqlCommand(query, cs);

        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }

    //Previous user stored the date time as ticks, have to convert back to DateTime
    DataTable dtCloned = new DataTable();
    dtCloned.Clear();
    dtCloned.Columns.Add("Time_Stamp", typeof(DateTime));
    dtCloned.Columns.Add("I1", typeof(int));

    foreach (DataRow dr in dt.Rows)
    {
        DataRow r = dtCloned.NewRow();
        r[0] = new DateTime((long)dr[0]);
        r[1] = dr[1];

        dtCloned.Rows.Add(r);
    }

    dtCloned.DefaultView.Sort = "Time_Stamp DESC";
    dtCloned = dtCloned.DefaultView.ToTable();

    return dtCloned;
}

**ABSOLUTELY POSITIVELY DO NOT** format strings to insert parameters. You need to be using a parameterized query, or you risk SQL Injection. [This question/answer goes over how to deal with that in your case](https://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter). — Clockwork-Muse, Feb 21 '19 at 00:02
This is an in house tool, I don't have to worry about SQL injection. — Baddack, Jun 13 '19 at 21:45

Chart from SQL Datasource but DateTime in Ticks

1 Answers1