iOS Which data type is better for Date in SQLite3

[mikezang]

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?

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

 

[seepel]

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.

 

[mikezang]

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.

 

[seepel]

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.

 

[mikezang]

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'

 

[PhoneyDeveloper]

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.

 

[mikezang]

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.

 

[ulbador]

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.

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.

 

[mikezang]

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.

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?

 

[ulbador]

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?

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.

 

[mikezang]

Thanks.

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