Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Nov 30, 2015 · 10 comments

Comments

@firebird-automations
Copy link

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
Copy link
Author

Modified by: @cincuranet

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

@firebird-automations
Copy link
Author

Modified by: @cincuranet

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

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10722 ]

@firebird-automations
Copy link
Author

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
Copy link
Author

Modified by: @cincuranet

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

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Author

Commented by: @cincuranet

And of course the XdrStream.ReadDateTime.

@firebird-automations
Copy link
Author

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
Copy link
Author

Commented by: Kamil Kocian (kociank)

Yes, exactly. Only "DateTimeToTimeSpan" need fixing.

And sorry for XdrStream.ReadDateTime.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

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

@firebird-automations
Copy link
Author

Modified by: @cincuranet

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

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\);
        \}
    \}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants