|Summary:||Mono.Data.Sqlite fails to store seconds in timestamp value to a table|
|Product:||[Mono] Class Libraries||Reporter:||Prashant [MSFT] <prashant.cholachagudd>|
|Component:||Mono.Data.Sqlite||Assignee:||Marek Safar <masafa>|
|Severity:||normal||CC:||adrian.murphy, amund, andrew, brendan.zagaeski, david, domen, eduardo_carisio, ggirard, james.white+bugzilla, joproulx, jrosano, mfulker, mono-bugs+mono, saurabhd, shuozhe.nan|
|Target Milestone:||4.6.1 (C8SR1)|
|Tags:||BZSRC8S1||Is this bug a regression?:||Yes|
|Last known good build:||cycle7|
Description Prashant [MSFT] 2016-09-15 05:37:41 UTC
Created attachment 17499 [details] Testcase 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 Runtime: Mono 4.6.0 (mono-4.6.0-branch/746756c) (64-bit) GTK+ 2.24.23 (Raleigh theme) Package version: 406000245 === NuGet === Version: 188.8.131.52 === Xamarin.Profiler === Not Installed === Xamarin.Android === Version: 184.108.40.206 (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: https://github.com/xamarin/AndroidDesigner.EPL === Xamarin Android Player === Version: 0.6.5 Location: /Applications/Xamarin Android Player.app === Xamarin Inspector === Version: 0.8.1.0 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: 10.0.0.6 (Visual Studio Enterprise) Hash: 6c3fee4 Branch: xcode8 Build date: 2016-09-09 13:01:32-0400 === Xamarin.Mac === Version: 220.127.116.11 (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 18.104.22.168.
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 22.214.171.1249, Mono 4.8.0 and Mono 126.96.36.199. 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 188.8.131.52 (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 184.108.40.206, 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 : https://github.com/mono/mono/commit/095701df579144a33d49fbe08ede8c7d3640a9a8 Which, before this bug was introduced, https://github.com/mono/mono/commit/04162ffbbdfed88ba298341f0384a35949b3c0f9 ...the string at index 7 was "yyyy-MM-dd HH:mm:ss.FFFFFFF" The fix for this bug was to use _datetimeFormats (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. Best, Brendan 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".