Which data type is better for Date in SQLite3

Discussion in 'iOS Programming' started by mikezang, Aug 28, 2010.

  1. mikezang macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #1
    I have data in format as below, now I try to convert both of them to SQLite3, I want to know which data type should be used when I create table in SQLite3, especially for Date, use Text or Numeric, which is better?
    Code:
    Public Type StockData1
        Date As Long
        Open As Long
        High As Long
        Low As Long
        Close As Long
        Volume As Double
    End Type
    
    Public Type StockData2
        Date As String //'20100828'
        Open As String 
        High As String 
        Low As String 
        Close As String 
        Volume As String 
    End Type
    
     
  2. seepel macrumors 6502

    seepel

    Joined:
    Dec 22, 2009
    #2
    It seems that it isn't all that important, see http://www.sqlite.org/datatype3.html. But, let me ask is there a reason you are using sqlite directly instead of Core Data? Core Data is quite streamlined unless your needs are extraordinary.
     
  3. mikezang thread starter macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #3
    I knew SQL and I want to finish my app as soon as possible, in next version, I might change to use Core Data.
    Because I have to learn about Core Data from zero, this will spend a lot of time for development.
     
  4. seepel macrumors 6502

    seepel

    Joined:
    Dec 22, 2009
    #4
    That's fair enough, but being familiar with SQL myself, I found that Core Data was pretty useful in speeding things up, and was basically the type of wrapper I would have written around SQLite anyway.
     
  5. mikezang thread starter macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #5
    Please give me some detail suggestion.

    I think that I can save date value as below to SQLite, I want to know which is better.

    1. integer as seconds since 1970
    2. integer as days since 1970
    3. string as '2010-09-03'
    4. string as '10-09-03'
     
  6. PhoneyDeveloper macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #6
    Which is better depends on your purpose. I would probably choose either your 1 or 3. Remember that there are time zone effects. You can use NSDataFormatter to convert between string and NSDate.

    If you want to explain the purpose of the dates you might get a better response.
     
  7. mikezang thread starter macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #7
    Those data are stock daily data, so I only need date without time, at the moment I only use it for local market, because I want to use it offline.

    I you need more information, pls let me know.
     
  8. ulbador macrumors 68000

    ulbador

    Joined:
    Feb 11, 2010
    #8

    For this purpose, just store it as text, per the sqlite link above. That way you won't have to do any fancy conversions or anything in code to display it and you can still sort it and such. If I needed to do calculations on it, I might store it as one of the other types, though for just display, text is fine.
     
  9. mikezang thread starter macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #9
    Now I am using hours since 1970, even so the sqlite3 file is near about 300MB, the size will be increased if use a char(10).

    By the way, I converted about 2000 stocks, every stock is about 2000 records, the converting spent about 5 hours, is this normal?
     
  10. ulbador macrumors 68000

    ulbador

    Joined:
    Feb 11, 2010
    #10
    That is probably the way to do it for your setup and that much data. The current unix time is 10 bytes in length and that will give you the date and time. The same date stored as text would only store the year-month-day

    I don't know what your table layout is, but when you are doing tons of rows of inserts like that with sqlite, it literally goes at least 50x to 100x faster if you batch them up into groups of transactions, like 1000 at a time.

    Like:
    begin;
    1000 inserts/updates/deletes
    commit;

    That's quite a bit of data to be warehousing in an sqlite DB on a phone.
     
  11. mikezang thread starter macrumors 6502a

    Joined:
    May 22, 2010
    Location:
    Tokyo, Japan
    #11
    Thanks.

    I had done it using your suggestion with "PRAGMA synchronous=OFF;", converting only spent minutes.
     

Share This Page