After the recent message on the forum about datetime formats, I had a look at what SQLite is doing to implement the new CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP keywords:
"These keywords resolve into ISO-8601 time strings. You can use them anywhere but they are especially useful as the default value for columns in a table definition."
I'm thinking we should revert back to storing as strings, but making sure that valid DateTime's are stored in ISO-8601 formats as well.
What do you think?
I should add, if we do this, we would probably need to add yet another compatibility field to the connection string...
As you said, SQLite 3.1.1 have new keywords CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP. So in my project, I modified the code of sqlite3.cs, added the compatibility of DateTime to storing as strings, It worked well.
BTW: I really want to be a member of this great project, if the team want to have a new one.
Hi szumic, If you could post a patch against the current cvs version, I'd appreciate it. I was about to start on a patch of my own :)
Alternatively, email it to me and I'll take a look at it and apply it to cvs.
You'll have to direct your request to become a developer directly to alexander, I think:)
I've added you to the project as a Developer.
Thinking further on this, for the sanity of those using SQLite.Net in existing apps, we probably need to make the new format a Compatibility Option. As I see it, we have 2 options here:
1. Create a Compatibility option like "Compatibility=[ISO8601-date-format]" and leave the current ticks based implementation as default,
2. make the new implementation the default, and provide a Compatibility option like "Compatibility=ticks-date-format"
So. Which is better? I think the second option is better in the long run. In fact, if we can get a consensus, perhaps we can do away with the old-date-format option altogether?
Sorry folks for my absense. I'm very busy last months.
I think, the main problem with DateTime is the lack of the native datetime format in SQLite. Whatever format we choose as default, nothing will stop users from embedding date/times in their own formats directly into SQL statement and complaining about it.
- add the option into the connection string called 'DateTimeFormat' with possible values of 'Ticks', 'ISO8601' and 'CurrentCulture'.
- have this option the default value of ISO8601
- DataReader.GetDateTime must support all formats no matter what option was set.