[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
> I am able to recreate the exception here. The value of Int64.MaxValue is > 9223372036854775807. The UnixEpoch values are measured in seconds from > the epoch 1970-01-01 00:00:00Z. Adding 9223372036854775807 seconds to the > UnixEpoch would result in a DateTime far beyond the allowed maximum value > for DateTime in the .NET Framework, which is -12-31 23:59:59.999. dotNet uses 0001-01-01 00:00:00.000 as the epoch and integer increment is hundredths of a microsecond since the epoch. This makes the largest dotNet timevalue 31550644800 which is a bit more than a third of Int64.MaxValue. The maximum seconds increment of the unix epoch is 253370851200 after which you will need 5 digits for the year, and the minimum value is -62135596800, the next second below which is 0001/12/31 23:59:59.00 BC.
[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
Makes sense, Somehow I had in my head that int64 would still only get you to the year 5000 or so. I guess the question is if it should fail like that in this case. Capping it at DateTime.MaxValue does not seem ideal either. Jann -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: 28 April 2016 19:54 To: 'SQLite mailing list' Subject: Re: [sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used Jann Roder wrote: > > I just tested the new version that should have the fix for this and > now I get an ArgumentOutOfRangeException exception when I actually > have a value of Int64.MaxValue in a date column. Stack trace: > I am able to recreate the exception here. The value of Int64.MaxValue is 9223372036854775807. The UnixEpoch values are measured in seconds from the epoch 1970-01-01 00:00:00Z. Adding 9223372036854775807 seconds to the UnixEpoch would result in a DateTime far beyond the allowed maximum value for DateTime in the .NET Framework, which is -12-31 23:59:59.999. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Winton Capital Management Limited (?Winton?) is a limited company registered in England and Wales with its registered offices at 16 Old Bailey, London, EC4M 7EG (Registered Company No. 3311531). Winton is authorised and regulated by the Financial Conduct Authority in the United Kingdom, registered as an investment adviser with the US Securities and Exchange Commission, registered with the US Commodity Futures Trading Commission and a member of the National Futures Association in the United States. This communication, including any attachments, is confidential and may be privileged. This email is for use by the intended recipient only. If you receive it in error, please notify the sender and delete it. You should not copy or disclose all or any part of this email. This email does not constitute an offer or solicitation and nothing contained in this email constitutes, and should not be construed as, investment advice. Prospective investors should request offering materials and consult their own advisers with respect to investment decisions and inform themselves as to applicable legal requirements, exchange control regulations and taxes in the countries of their citizenship, residence or domicile. Past performance is not indicative of future results. Winton takes reasonable steps to ensure the accuracy and integrity of its communications, including emails. However Winton accepts no liability for any materials transmitted. Emails are not secure and cannot be guaranteed to be error free. Winton handles personal information in accordance with its privacy notice.<https://www.wintoncapital.com/en/regulatory-disclosures#privacypolicy>
[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
Jann Roder wrote: > > I just tested the new version that should have the fix for this and now > I get an ArgumentOutOfRangeException exception when I actually have a > value of Int64.MaxValue in a date column. Stack trace: > I am able to recreate the exception here. The value of Int64.MaxValue is 9223372036854775807. The UnixEpoch values are measured in seconds from the epoch 1970-01-01 00:00:00Z. Adding 9223372036854775807 seconds to the UnixEpoch would result in a DateTime far beyond the allowed maximum value for DateTime in the .NET Framework, which is -12-31 23:59:59.999. -- Joe Mistachkin
[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
Hi Joe, I just tested the new version that should have the fix for this and now I get an ArgumentOutOfRangeException exception when I actually have a value of Int64.MaxValue in a date column. Stack trace: at System.DateTime.Add(Double value, Int32 scale) at System.Data.SQLite.SQLiteConvert.UnixEpochToDateTime(Int64 seconds, DateTimeKind kind) at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index) at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ) at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i) at System.Data.SQLite.SQLiteDataReader.GetValues(Object[] values) at System.Data.ProviderBase.SchemaMapping.LoadDataRow() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) Jann -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: 02 March 2016 18:36 To: 'SQLite mailing list' Subject: Re: [sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used Jann Roder wrote: > > When I set the dateformat to unixepoch the maximum date I can get back > from a query is 2038-01-19. This appears to be a problem in the > wrapper as SQLite itself is using 64bit integers for dates. > Thanks for the clear and concise report. This issue should now be fixed on trunk, here: https://system.data.sqlite.org/index.html/info/d28375563e7f5774 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Winton Capital Management Limited (?Winton?) is a limited company registered in England and Wales with its registered offices at 16 Old Bailey, London, EC4M 7EG (Registered Company No. 3311531). Winton is authorised and regulated by the Financial Conduct Authority in the United Kingdom, registered as an investment adviser with the US Securities and Exchange Commission, registered with the US Commodity Futures Trading Commission and a member of the National Futures Association in the United States. This communication, including any attachments, is confidential and may be privileged. This email is for use by the intended recipient only. If you receive it in error, please notify the sender and delete it. You should not copy or disclose all or any part of this email. This email does not constitute an offer or solicitation and nothing contained in this email constitutes, and should not be construed as, investment advice. Prospective investors should request offering materials and consult their own advisers with respect to investment decisions and inform themselves as to applicable legal requirements, exchange control regulations and taxes in the countries of their citizenship, residence or domicile. Past performance is not indicative of future results. Winton takes reasonable steps to ensure the accuracy and integrity of its communications, including emails. However Winton accepts no liability for any materials transmitted. Emails are not secure and cannot be guaranteed to be error free. Winton handles personal information in accordance with its privacy notice.<https://www.wintoncapital.com/en/regulatory-disclosures#privacypolicy>
[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
Hi,
When I set the dateformat to unixepoch the maximum date I can get back from a
query is 2038-01-19. This appears to be a problem in the wrapper as SQLite
itself is using 64bit integers for dates.
You can reproduce the problem like this:
[Test]
public void ShouldDoSparseTimeSeriesTransform2()
{
ExecuteNonQuery("CREATE TABLE Test_RAW (Date DATETIME, Id INT, Value INT,
PRIMARY KEY (Date, Id))");
ExecuteNonQuery("INSERT INTO Test_RAW VALUES(strftime('%s', '2038-01-20'),
1, 1)");
var result = ExecuteQuery("SELECT * FROM Test_RAW");
// Set breakpoint here and inspect the result.
Assert.AreEqual(1, result.Rows.Count);
}
private void ExecuteNonQuery(string command)
{
using (var cmd = new SQLiteCommand(command, m_connection))
{
cmd.ExecuteNonQuery();
}
}
private DataTable ExecuteQuery(string query)
{
using (var cmd = new SQLiteCommand(query, m_connection))
{
using (var da = new SQLiteDataAdapter(cmd))
{
var dataTable = new DataTable();
da.Fill(dataTable);
return dataTable;
}
}
}
private SQLiteConnection m_connection;
internal static SQLiteConnection GetNewInMemoryConnection()
{
var builder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
DateTimeFormat = SQLiteDateFormats.UnixEpoch
};
return new SQLiteConnection(builder.ConnectionString);
}
[SetUp]
public void SetUp()
{
m_connection = GetNewInMemoryConnection();
m_connection.Open();
}
[TearDown]
public void TearDown()
{
m_connection.Close();
m_connection.Dispose();
}
Winton Capital Management Limited ("Winton") is a limited company registered in
England and Wales with its registered offices at 16 Old Bailey, London, EC4M
7EG (Registered Company No. 3311531). Winton is authorised and regulated by the
Financial Conduct Authority in the United Kingdom, registered as an investment
adviser with the US Securities and Exchange Commission, registered with the US
Commodity Futures Trading Commission and a member of the National Futures
Association in the United States.
This communication, including any attachments, is confidential and may be
privileged. This email is for use by the intended recipient only. If you
receive it in error, please notify the sender and delete it. You should not
copy or disclose all or any part of this email.
This email does not constitute an offer or solicitation and nothing contained
in this email constitutes, and should not be construed as, investment advice.
Prospective investors should request offering materials and consult their own
advisers with respect to investment decisions and inform themselves as to
applicable legal requirements, exchange control regulations and taxes in the
countries of their citizenship, residence or domicile. Past performance is not
indicative of future results.
Winton takes reasonable steps to ensure the accuracy and integrity of its
communications, including emails. However Winton accepts no liability for any
materials transmitted. Emails are not secure and cannot be guaranteed to be
error free. Winton handles personal information in accordance with our privacy
notice.
[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used
Jann Roder wrote: > > When I set the dateformat to unixepoch the maximum date I can get back > from a query is 2038-01-19. This appears to be a problem in the wrapper > as SQLite itself is using 64bit integers for dates. > Thanks for the clear and concise report. This issue should now be fixed on trunk, here: https://system.data.sqlite.org/index.html/info/d28375563e7f5774 -- Joe Mistachkin
