Designing SQLite Database without Date format

Question

I am trying to store Date in sqlite database in android mm/dd/yyyy , I have read about this

What happens if I don't have HH:mm:ss (which is not needed for my application)?.
Is avoiding the date format of database by splitting it up a method used in the industry? ex:

Db Columns:
_id INTEGER;
data TEXT;
day INTEGER;
month INTEGER;
year INTEGER;

Thanks for any advice.

What do you want to do with the Date in the DB? I had some problems storing such dates in an mysql db with HH:mm:ss = 00:00:00. I woud consider to store it as String yyyyMMdd (this still keeps the correct order for sorting) — MrSmith42, Dec 23 '12 at 20:19

score 0 · Accepted Answer · answered Dec 23 '12 at 20:19

0

You could store the Date in milliseconds. Then build the appropriate format according the user's Locale at run time.

answered Dec 23 '12 at 20:19

Sam

86,580
20
181
179

score 0 · Answer 2 · answered Dec 23 '12 at 20:32

0

If you wand to use ony little memory, consider to store ddMMyyyy in a single intvariable.

either easy to read in logoutput

day+month*100+year*10000

day, month year can be extracted by devision and modulo operations

or for faster to handle for the computer

day&0x1F+(month<<5)&0xF+(year<<9)

day, month year can be extracted by >> and & and operations

answered Dec 23 '12 at 20:32

MrSmith42

9,961
6
38
49

In both cases the 'before' and 'after' relation is mapped to '<' and '>' – MrSmith42 Dec 23 '12 at 20:36

score 0 · Answer 3 · answered Dec 23 '12 at 20:58

0

For compatibility reasons, my app stores dates as strings, in ISO "YYYY-MM-DD" format. This is a special case though. For most purposes, Sam is the man.

answered Dec 23 '12 at 20:58

Philip Sheard

5,789
5
27
42

score 0 · Answer 4 · answered Dec 23 '12 at 20:58

0

SQLite's date functions also understand date strings in yyyy-mm-dd format.

Splitting up dates into their three fields is possible, but then you will not be able to easily do computations on them.

answered Dec 23 '12 at 20:58

CL.

173,858
17
217
259

Designing SQLite Database without Date format

4 Answers4