Re: [sqlite] DateTime kind stored as undefined

[mnie]

Hi Cezary,

indeed I try your piece of code (to retrieve data) and it works as expected,
kind is stored in db. It seems that it is a problem with dapper instead of
SqLite. So I will bump issue in Dapper
(https://github.com/StackExchange/Dapper/issues/571). Many thanks for help!

Best regards,
Michał



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Cezary H. Noweta]

Hello,

> my connection string looks like this:
> *var connection = new SQLiteConnection("DateTimeKind=Utc;Data
> Source=:memory:");*
> Here is a blog post about it (settig datetimekind in utc for sqlite) on
> which I based:
> 
https://www.thomaslevesque.com/2015/06/28/how-to-retrieve-dates-as-utc-in-sqlite/


Still, I cannot reproduce te original problem. The following results are OK:

==
test.cs:
==
using System;
using System.Data.SQLite;

class Test
{
static void TestUtc(bool utc, bool ticks)
{
DateTime dtUtcNow = DateTime.UtcNow;
DateTime dt;
SQLiteConnection connection;
SQLiteCommand cmd;
SQLiteDataReader rdr;
int rows;
string connectionstring = "Data Source=:memory:;";

Console.WriteLine("* Testing " + (utc ? "UTC" : "UNSPECIFIED") 
+ "/" + (ticks ? "TICKS" : "ISO8601") + "...");


Console.WriteLine("DateTime: {0}", dtUtcNow);
Console.WriteLine("DateTime.Kind: {0}", dtUtcNow.Kind);

if ( utc ) {
  connectionstring += "DateTimeKind=Utc;";
}
if ( ticks ) {
  connectionstring += "DateTimeFormat=Ticks;";
}
connection = new SQLiteConnection(connectionstring);
connection.Open();
cmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [test] 
([date] DATETIME NOT NULL);");

cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("INSERT INTO [test] ([date]) VALUES 
(@date);");

cmd.Parameters.AddWithValue("@date", dtUtcNow);
cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("SELECT [date] FROM [test];");
cmd.Connection = connection;
rdr = cmd.ExecuteReader();

while ( rdr.Read() ) {
dt = rdr.GetDateTime(0);
Console.WriteLine("DateTime: {0}", dt);
Console.WriteLine("DateTime.Kind: {0}", dt.Kind);
}
rdr.Dispose();
cmd.Dispose();
connection.Dispose();
}

static void Main()
{
TestUtc(false, false);
TestUtc(true, false);
TestUtc(false, true);
TestUtc(true, true);
}

}
==

gives the following results:

==
* Testing UNSPECIFIED/ISO8601...
DateTime: 2017-12-16 15:48:39
DateTime.Kind: Utc
DateTime: 2017-12-16 16:48:39
DateTime.Kind: Unspecified
* Testing UTC/ISO8601...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
* Testing UNSPECIFIED/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Unspecified
* Testing UTC/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
==

It looks that your app behaves as if it had default settings: 
ISO8601/Unspecified, which results in Local/Unspecified. The sole thing 
I have in mind is Connection Designer. Try to omit the Connection 
Designer -- as far as I remember it had problems with some parameters: 
CD doubled them or had named them incorrectly.


-- best regards

Cezary H. Noweta

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Cezary H. Noweta]

I'm sorry -- the following post was sent to a private e-mail by an accident:

Hello,

On 2017-12-13 12:51, Michał Niegrzybowski wrote:
> I have a table which has a column of type DateTime in my code I insert
> there an actual UTC Date (which is not the same as my local time). When I
> want to gather previously added record, my record contains date in his
> DateTime column, but this DateTime is a localtime with kind specified to
> 'undefined' instead of 'UTC'.


I cannot reproduce the problem. Setting a format to ticks and a kind to 
UTC causes a storing/retrieving a valid UTC DateTime, which is stored as 
INTEGER. Could you provide your connection string?


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Simon Slavin]

On 13 Dec 2017, at 11:51am, Michał Niegrzybowski 
 wrote:

> I have a table which has a column of type DateTime in my code I insert
> there an actual UTC Date (which is not the same as my local time). When I
> want to gather previously added record, my record contains date in his
> DateTime column, but this DateTime is a localtime with kind specified to
> 'undefined' instead of 'UTC'.

Can you tell us the datatype of that column ?  If you want to post the CREATE 
TABLE command, that’s fine too.

Can you use the SQLite shell tool to look at the values stored in that field 
without System.Data.SqLite getting in the way ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users