sqlite date and time format problem

4 posts, 0 answers
  1. serkanp
    serkanp avatar
    14 posts
    Member since:
    Jul 2007

    Posted 20 Jan 2011 Link to this post

    i have a sqlite3 db and inside a table i have a column named "saat" as "time" format , and a column name "tarih" as "date" format and "createdDate" as "timestamp" format. 
    i am using orm 2010.3.1125.1  
    when i use the wizard, it detects time , date formats as decimal 
    but the timestamp format as datetime
    so i changed the rlinq file , i changed decimal with datetime in "tarih" column. "saat" as timespan
    also i changed the class file automatically generated.. 
     but not worked, i tried different datatypes, date, datetime etc..  both in rlinq and class file
    but i get no success..

    what are the correct formats for "date" and "time" formats?
     
  2. serkanp
    serkanp avatar
    14 posts
    Member since:
    Jul 2007

    Posted 20 Jan 2011 Link to this post

    also i noticed a very interesting case.. 
    i wanted to attach the db but forum does not allow it .. anyway let me tell the story...
    i have a table named users
    it has 3 columns
    username as nvarchar 50, userlogin as nvarchar 50, createdDate as timeStamp

    i have 3 records , i inserted 3 rows with orm, i set the createdDate with DateTime.Now , when i make a select query (select createdDate from users) with sqlite administrator, all createdDate columns are interestingly crazy values (one of them 30.12.1899 00:03:17 and the other is seems empty-no data-).  but the datetime.now gives 20.01.2011 13:09:00 in visual studio
    but when i make a select query like "select datetime(createdDate) from users" it shows the dates correctly. 
    but again interestingly, when i get the data with orm, it shows the dates correctly as i wanted.. 

    then i changed one row's createdDate column manually (i am using sqlite administrator)
    when i run a select command, my manually changed column shown correct but the others are wrong again in sqlite administrator. 
    but now, when i get the data with orm,
         Dim t As Sqlitest = New Sqlitest
         For Each obje In t.Users
                Debug.Print(obje.UserLogin & "=" & obje.CreatedDate)
         Next
    (before manually changing 1 row's createdDate column, this code worked perfect)
     it gives an interesting error : 
    Error reading field ormtest1.User._createdDate from ResultSet: System.FormatException: Input string was not in a correct format.
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
       at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
       at System.Convert.ToDouble(String value, IFormatProvider provider)
       at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
       at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
       at System.Data.SQLite.SQLiteDataReader.GetDateTime(Int32 i)
       at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.ReadValue(DataHolder& data)
       at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.Read(DataHolder& data)
       at OpenAccessRuntime.Relational.metadata.RelationalColumn.GetValue(DataHolder& data)
       at OpenAccessRuntime.Relational.RelationalGenericState.GetFieldDataPass1(DataHolder& holder, RelationalField field)
       at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.FormatException: Input string was not in a correct format.
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
       at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
       at System.Convert.ToDouble(String value, IFormatProvider provider)
       at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
       at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
       at System.Data.SQLite.SQLiteDataReader.GetDateTime(Int32 i)
       at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.ReadValue(DataHolder& data)
       at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.Read(DataHolder& data)
       at OpenAccessRuntime.Relational.metadata.RelationalColumn.GetValue(DataHolder& data)
       at OpenAccessRuntime.Relational.RelationalGenericState.GetFieldDataPass1(DataHolder& holder, RelationalField field)
       at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)


    what is wrong ? :)))
  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 21 Jan 2011 Link to this post

    Hi Serkan,

    this is interesting. However, SQLite is a bit difficult / weak when it comes to datetime, as strings are used for the underlying representation in the database. This means, that the string value semantics takes over at times. In your case it seems to me, that 'old' dates will be falsely interpreted as double which fails.
    I will look into this, maybe there is a config option for that on the driver level. 

    Best wishes,
    Thomas
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 25 Jan 2011 Link to this post

    Hi serkanp,

    I think the problem is that the database is using the DateTimeFormat=JulianDay option, but OpenAccess wants the ISO8601 format. Julian Day is internally (in SQLite) held as a double value (most likely in a string form) and then converted to/from a System.DateTime. A database can only be opened in one way or the other and you cannot mix the different styles, and the stored (string) representation must be comparable and consistent; otherwise bad behavior can happen.
    I propose to make sure that your tools and your application use the same ISO8601 format; this is also the best format for human readability.

    Greetings,
    Thomas
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top