DateFormat revisited

Developers
2005-02-06
2013-04-15
  • Robert Foster
    Robert Foster
    2005-02-06

    Hi,
    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?

     
    • Robert Foster
      Robert Foster
      2005-02-06

      I should add, if we do this, we would probably need to add yet another compatibility field to the connection string...

       
    • Mic
      Mic
      2005-02-07

      hello robertjf,
      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.

      Thanks
      mic

       
      • Robert Foster
        Robert Foster
        2005-02-10

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

        Rob.

         
      • I've added you to the project as a Developer.

        Alexander

         
    • Robert Foster
      Robert Foster
      2005-02-10

      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.

        My proposition:
        - 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.