How to get MySQL time fraction with Devart dotConnect Express MySQL?

Question

Assume that I have a table like this:

CREATE TABLE `table1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `date` date,
  `timestamp2` timestamp(2),
  `datetime3` datetime(3),
  `time6` time(6),
  PRIMARY KEY (`id`)
);

Here's the sample inserts:

insert into table1(`date`,`timestamp2`,datetime3,time6)
values('2000-01-01','2001-01-01 01:01:01.23',
       '2002-01-01 01:01:01.243','01:01.234893');

insert into table1(`date`,`timestamp2`,datetime3,time6) 
values(null,null,null,null);

and I get the data in C# like this:

using Devart.Data.MySql;

DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "select * from table1;";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dt);
        conn.Close();
    }
}

int row = 0;

foreach (DataRow dr in dt.Rows)
{
    row++;

    sb.AppendLine();
    sb.AppendLine("Row " + row);

    foreach (DataColumn dc in dt.Columns)
    {
        object ob = dr[dc.ColumnName];
        string typeName = ob.GetType().ToString();
        sb.AppendLine($"{dc.ColumnName} = {typeName} | value = " + ob + "");
    }
}

richTextBox1.Text = sb.ToString();

and here's the output:

Row 1
id = System.Int64 | value = 1
date = System.DateTime | value = 01/01/2000 12:00:00 AM
timestamp2 = System.DateTime | value = 01/01/2001 1:01:01 AM
datetime3 = System.DateTime | value = 01/01/2002 1:01:01 AM
time6 = System.TimeSpan | value = 01:01:00.2348930

Row 2
id = System.Int64 | value = 2
date = System.DBNull | value = 
timestamp2 = System.DBNull | value = 
datetime3 = System.DBNull | value = 
time6 = System.DBNull | value =

As you can see that the Devart Express dotConnect MySQL returns time fraction in DateTime in C#. The time fraction is lost.

How to get the time fraction?

*Note: For some reason I must use Devart Express dotConnect.

score 2 · Accepted Answer · answered Jan 20 '23 at 16:32

Fractional second values are stored in DataTime structure.

Kindly note, that the default ToString method of DateTime doesn't show the fraction of a second.

To extract the string representation of a time's millisecond component, call the date and time value's DateTime.ToString(String) or ToString method, and pass the fff or FFF custom format pattern alone or with other custom format specifiers as the format parameter: https://learn.microsoft.com/en-us/dotnet/standard/base-types/how-to-display-milliseconds-in-date-and-time-values.

To see the time fraction in your test application:

foreach (DataColumn dc in dt.Columns)
{
    var ob = dr[dc.ColumnName];
    string typeName = ob.GetType().ToString();

    string val;
    if (typeName == "System.DateTime") val = ((DateTime)ob).ToString("yyyy.MM.dd hh: mm:ss.fff");
    else val = ob.ToString();

    Console.WriteLine($"{dc.ColumnName} = {typeName} | value = " + val + "");
}

The result:

Row 1
id = System.Int64 | value = 1
date = System.DateTime | value = 2000.01.01 12: 00:00.000
timestamp2 = System.DateTime | value = 2001.01.01 01: 01:01.230
datetime3 = System.DateTime | value = 2002.01.01 01: 01:01.243
time6 = System.TimeSpan | value = 01:01:00.2348930
 
Row 2
id = System.Int64 | value = 2
date = System.DBNull | value =
timestamp2 = System.DBNull | value =
datetime3 = System.DBNull | value =
time6 = System.DBNull | value =
Press any key to continue . . .

How to get MySQL time fraction with Devart dotConnect Express MySQL?

1 Answers1