Trimming ten-thousandths of a second during TIMESPAN conversion to the DateTime in the NET Provider for Firebird. [DNET654]

[firebird-automations]

Submitted by: Kamil Kocian (kociank)

The NET Provider for Firebird trims ten-thousandths of a second during TIMESPAN conversion to the .NET DateTime type in method TypeDecoder.DecodeTime(int sql_time). Trimming the ten-thousandths of a second is causing problem when comparing time read from Firebird to .NET and using the value again in SQL statements.

However it is possible to initialize .NET type DateTime(or TimeSpan) with number of ticks, this way keeping the precision of Firebird TIMESTAMP in .NET.

Problematic source code in FirebirdSql.Data.FirebirdClient\Common\TypeDecoder.cs :
public static TimeSpan DecodeTime(int sql_time)
{
int millisInDay = sql_time / 10;
int hour = millisInDay / 3600000;
int minute = (millisInDay - hour * 3600000) / 60000;
int second = (millisInDay - hour * 3600000 - minute * 60000) / 1000;
int millisecond = millisInDay - hour * 3600000 - minute * 60000 - second * 1000;

return new TimeSpan\(0, hour, minute, second, millisecond\);

}

Commits: a1e4f43 6fbf088 43bca52

[firebird-automations]

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10722 ]

[firebird-automations]

Commented by: Kamil Kocian (kociank)

Thank you for your solution however the problem has not been solved completely yet. There is still a problem with a DateTime construction, where time precision is lost.
E.g during conversion of Firebird's Timestamp in method "protected override System.Array FirebirdSql.Data.Client.ExternalEngine.DecodeSlice(byte[] slice)" the new DateTime is composed using milliseconds "DateTime timestamp = new System.DateTime( date.Year, date.Month, date.Day, time.Hours, time.Minutes, time.Seconds, time.Milliseconds);".

Included list of afflicted methods :

// Receiving data from Firebird
protected override System.Array FirebirdSql.Data.Client.ExternalEngine.ExtArray.DecodeSlice(byte[])
protected override System.Array FirebirdSql.Data.Client.Native.FesArray.DecodeSlice(byte[])
public void FirebirdSql.Data.Common.DbField.SetValue(byte[])

// Sending data to Firebird
public static TimeSpan FirebirdSql.Data.Common.TypeHelper.DateTimeToTimeSpan(System.DateTime)
private byte[] FirebirdSql.Data.Client.ExternalEngine.ExtArray.EncodeSlice(FirebirdSql.Data.Common.ArrayDesc, System.Array, int)
private byte[] FirebirdSql.Data.Client.Native.FesArray.EncodeSlice(FirebirdSql.Data.Common.ArrayDesc, System.Array, int)
public int FirebirdSql.Data.Common.DbValue.GetTime()
public byte[] FirebirdSql.Data.Common.DbValue.GetBytes()

[firebird-automations]

Modified by: @cincuranet

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

[firebird-automations]

Commented by: @cincuranet

And of course the XdrStream.ReadDateTime.

[firebird-automations]

Commented by: @cincuranet

What's wrong with these:

private byte[] FirebirdSql.Data.Client.ExternalEngine.ExtArray.EncodeSlice(FirebirdSql.Data.Common.ArrayDesc, System.Array, int)
private byte[] FirebirdSql.Data.Client.Native.FesArray.EncodeSlice(FirebirdSql.Data.Common.ArrayDesc, System.Array, int)
public int FirebirdSql.Data.Common.DbValue.GetTime()
public byte[] FirebirdSql.Data.Common.DbValue.GetBytes()
?

These eventually call `DateTimeToTimeSpan` so only this one need fixing, no?

[firebird-automations]

Commented by: Kamil Kocian (kociank)

Yes, exactly. Only "DateTimeToTimeSpan" need fixing.

And sorry for XdrStream.ReadDateTime.

[firebird-automations]

Commented by: @cincuranet

Do you mind writing tests for these? Probably somewhere in FbCommandTests.

[firebird-automations]

Modified by: @cincuranet

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Commented by: Kamil Kocian (kociank)

Sorry, I was without access to VS2015.

Do you mean something like this (implemented in FbCommandTests) :

    \[Test\]
    public void ReadsDateTimeWithProperPrecision\(\)
    \{
        using \(var cmd = Connection\.CreateCommand\(\)\)
        \{
            cmd\.CommandText = "select cast\('1\.2\.2015 05:06:01\.4321' as timestamp\) from rdb$database";
            var result = \(DateTime\)cmd\.ExecuteScalar\(\);
            Assert\.AreEqual\(new DateTime\(635583639614321000\), result\);
        \}
    \}

    \[Test\]
    public void PassesDateTimeWithProperPrecision\(\)
    \{
        DateTime dt = new DateTime\(635583639614321000\);
        using \(var cmd = Connection\.CreateCommand\(\)\)
        \{
            cmd\.CommandText = "select cast\(@value as timestamp\) from rdb$database";
            cmd\.Parameters\.Add\("value", dt\);
            var result = \(DateTime\)cmd\.ExecuteScalar\(\);
            Assert\.AreEqual\(dt, result\);
        \}
    \}