Bug 44349 - Mono.Data.Sqlite fails to store seconds in timestamp value to a table
Summary: Mono.Data.Sqlite fails to store seconds in timestamp value to a table
Alias: None
Product: Class Libraries
Classification: Mono
Component: Mono.Data.Sqlite ()
Version: 4.6.0 (C8)
Hardware: PC Mac OS
: Highest normal
Target Milestone: 4.6.1 (C8SR1)
Assignee: Marek Safar
Depends on:
Reported: 2016-09-15 05:37 UTC by Prashant [MSFT]
Modified: 2016-12-01 08:37 UTC (History)
15 users (show)

Tags: BZSRC8S1
Is this bug a regression?: Yes
Last known good build: cycle7

Testcase (34.21 KB, application/zip)
2016-09-15 05:37 UTC, Prashant [MSFT]

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.

Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:

Description Prashant [MSFT] 2016-09-15 05:37:41 UTC
Created attachment 17499 [details]

In the latest Cycle 8 release when we insert the DateTime the seconds value from the date time is ignored.
The timestamp is always stored with :00 for seconds

Test case attached

#Version Information

=== Xamarin Studio Enterprise ===

Version 6.1 (build 5441)
Installation UUID: 2b46b4af-74ff-40c0-962a-1c8c713c249f
	Mono 4.6.0 (mono-4.6.0-branch/746756c) (64-bit)
	GTK+ 2.24.23 (Raleigh theme)

	Package version: 406000245

=== NuGet ===


=== Xamarin.Profiler ===

Not Installed

=== Xamarin.Android ===

Version: (Visual Studio Enterprise)
Android SDK: /Users/prashant/android-sdk-mac_x86
	Supported Android versions:
		2.3    (API level 10)
		4.0.3  (API level 15)
		4.1    (API level 16)
		4.2    (API level 17)
		4.3    (API level 18)
		4.4    (API level 19)
		4.4.87 (API level 20)
		5.0    (API level 21)
		5.1    (API level 22)
		6.0    (API level 23)
		7.0    (API level 24)

SDK Tools Version: 25.2.2
SDK Platform Tools Version: 24.0.3
SDK Build Tools Version: 23.0.3

Java SDK: /usr
java version "1.8.0_20"
Java(TM) SE Runtime Environment (build 1.8.0_20-b26)
Java HotSpot(TM) 64-Bit Server VM (build 25.20-b23, mixed mode)

Android Designer EPL code available here:

=== Xamarin Android Player ===

Version: 0.6.5
Location: /Applications/Xamarin Android Player.app

=== Xamarin Inspector ===

Hash: 95792d1
Branch: master
Build date: Thu May 12 22:20:04 UTC 2016

=== Apple Developer Tools ===

Xcode 8.0 (11246)
Build 8A218a

=== Xamarin.iOS ===

Version: (Visual Studio Enterprise)
Hash: 6c3fee4
Branch: xcode8
Build date: 2016-09-09 13:01:32-0400

=== Xamarin.Mac ===

Version: (Visual Studio Enterprise)

=== Build Information ===

Release ID: 601005441
Git revision: 68292d1ab289911c815ddc715dd7cc29a9752f9f
Build date: 2016-09-09 04:43:23-04
Xamarin addins: ed25d008672663eeb9db55f1ccecb3c24d2fd3b2
Build lane: monodevelop-lion-cycle8

=== Operating System ===

Mac OS X 10.12.0
Darwin Prashant-MBPr 16.0.0 Darwin Kernel Version 16.0.0
    Mon Aug 29 17:56:20 PDT 2016
    root:xnu-3789.1.32~3/RELEASE_X86_64 x86_64
Comment 2 andrew 2016-09-19 17:13:17 UTC
I get the same issue after updating to Xamarin Android
Comment 3 Eduardo Carísio 2016-10-04 21:08:55 UTC
Same here, no seconds and milliseconds...
Comment 4 Marek Safar 2016-10-12 10:14:49 UTC
Fixed in master, Mono 4.8, Mono 4.6
Comment 5 Saurabh 2016-10-12 12:25:37 UTC
I have checked this issue with latest Master Mono, Mono 4.8.0 and Mono I am still getting 00 for second. This is the screencast for the same: http://screencast.com/t/PxNpNdeTITJ

Environment details and Info: https://gist.github.com/saurabh360/f4df84b5bef93c5051fde783ee4e1070
Comment 6 Marek Safar 2016-10-13 07:34:09 UTC
Please use products version which have the fix included before reopening the ticket.
Comment 7 Brendan Zagaeski (Xamarin Team, assistant) 2016-10-19 23:22:19 UTC
The screencast from Comment 5 shows testing on Xamarin.Android.  And the Xamarin.Android version listed in the attached environment info is (96c7ba6c844dac58d02210688d879b794602c3db).

That Xamarin.Android version references Mono commit ef43c151e2bfee5f1e86ea3d6e375472028a013a.  That Mono commit happened on October 1, so it predates the discussion of the fix in Comment 4.

It appears that the corresponding commits for Comment 4 are:

> master:            https://github.com/mono/mono/commit/095701df579144a33d49fbe08ede8c7d3640a9a8
> mono-4.6.0-branch: https://github.com/mono/mono/commit/6cfb6363e7f1fdb4c58f485b194a0966c916e40f
> mono-4.8.0-branch: https://github.com/mono/mono/commit/5cbb67c54cd1862785ebe31f581a0044b876b43c
Comment 8 Saurabh 2016-10-24 10:51:35 UTC
I have checked this Issue with latest Cycle8SR1 build X.Android, Now I am getting value for second in DateTime. This is the screencast for the same: http://screencast.com/t/dy1TqoXKTq7r

Environment Info and Logs: https://gist.github.com/saurabh360/d1830c1e399e2b780859e0a97986b762
Comment 9 mfulker 2016-10-25 16:04:22 UTC
I notice that in the original code it was using the format string "yyyy-MM-dd HH:mm:ss.FFFFFFF" and with the fix it's using the format string "yyyy-MM-dd HH:mm:ss.FFFFFFFK" (with a K).

Is this intentional?

(I seem to be encountering some discrepancies with DateTimeKind between the latest version and the January version.)
Comment 10 Marek Safar 2016-10-26 08:26:03 UTC
What original code?
Comment 11 mfulker 2016-10-26 13:55:06 UTC
Before the fix for this bug, this code was accessing _datetimeFormats[7] :

Which, before this bug was introduced,
...the string at index 7 was "yyyy-MM-dd HH:mm:ss.FFFFFFF"

The fix for this bug was to use _datetimeFormats[5] (which has seconds and milliseconds). But it's not the same format string used before commit 04162ffbbdfed88ba298341f0384a35949b3c0f9
Comment 12 Shuozhe Nan 2016-11-30 18:02:40 UTC
We are having some major problems with this currently. Within our application, timestamps are written into the SQLite database with the GMT offset (e.g. '+01:00'), for both UTC and localtime since the last update.

Within the application the timestamp are read correctly, but once we transfer it to our MSSQL server and link it via sqliteodbc, the offset is being applied to the timestamp and we travel one hour back into the past..
Comment 13 mfulker 2016-11-30 18:07:53 UTC
We gave up on waiting for a correct fix and ended up working around it by passing every instance of a DateTime parameter into the DbCommand object as a string instead of a DateTime, using ToString("yyyy-MM-dd HH:mm:ss.FFFFFFF") .
Comment 14 Brendan Zagaeski (Xamarin Team, assistant) 2016-11-30 21:38:10 UTC
In reply to Comment 12:

> timestamps are written into the SQLite database with the GMT offset (e.g. '+01:00')

That sounds like it could be a separate issue.  This Bug 44349 is about a behavior where ":00" was always being stored for the seconds value of the time stamp.  Although it is possible that changes to the time offset might be related to the same code path that caused that original symptom, the new symptom is sufficiently different from this original bug that I would strongly recommend filing it under its own bug report.  Thanks!

* * *

For the older issue from Comment 9 about the extra "K", that issue is definitely closely related to this original bug.  Even in that case, it wouldn't hurt to file the symptom under its own follow-up bug that refers back to this bug.  I won't have a chance to take care of re-filing that issue myself right away (since I'd need to have enough time to make sure I could replicate the issue myself, and I'd also need to brush-up on using Mono.Data.Sqlite), but I'll aim to get back around to filing that follow-up bug within the next few weeks if no one beats me to it.

Xamarin Customer Support
Comment 15 Shuozhe Nan 2016-12-01 08:37:13 UTC
> timestamps are written into the SQLite database with the GMT offset (e.g. '+01:00')

Sorry I forgot to say that '+01:00' is the result of the extra "K".