DateTime kind stored as undefined

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

DateTime kind stored as undefined

mnie
Hi All,

I encounter a problem with inserting a DateTime.UtcNow to a database, after
upgrade from System.Data.SqLite.x64 ver. 1.0.76 to System.Data.SqLite.Core
ver. 1.0.106.
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'.
My codebase is in C#. I try to specify DateTimeKind in connection string or
DateTimeFormat as ticks but they aren't works.
Is this a bug in SqLite(I suppose yes), or maybe I have to do something
extra to make it works?

Best Regards,
Michał Niegrzybowski
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DateTime kind stored as undefined

Simon Slavin-3


On 13 Dec 2017, at 11:51am, Michał Niegrzybowski <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users