This is Xamarin's bug tracking system. For product support, please use the support links listed in your Xamarin Account.
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
Status: VERIFIED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: Mono.Data.Sqlite (show other bugs)
Version: 4.6.0 (C8)
Hardware: PC Mac OS
: Highest normal
Target Milestone: 4.6.1 (C8SR1)
Assignee: Marek Safar
URL:
Depends on:
Blocks:
 
Reported: 2016-09-15 05:37 UTC by Prashant [MSFT]
Modified: 2016-12-01 08:37 UTC (History)
15 users (show)

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


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

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: 3.4.3.0

=== Xamarin.Profiler ===

Not Installed

=== Xamarin.Android ===

Version: 7.0.0.18 (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: 2.10.0.99 (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 7.0.0.18.
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 4.9.0.469, Mono 4.8.0 and Mono 4.6.1.10. 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 Support) 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 7.0.1.3 (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 7.0.2.28, 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] :
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[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 Support) 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".

Note You need to log in before you can comment on or make changes to this bug.