nanjero echizen nanjero echizen - 2 months ago 11
Android Question

Android - How to "properly" store Date and Time in SQLite?

I have read a few other posts regarding this question. Majority of answers suggest turning the date/time data into a certain format (UTC) and storing it in a string dataType.

Would it be "appropriate" to store them in integer format and 1 column for each data (year, month, day, hour, minutes) ? Or is this not acceptable ? because this would make comparison to current date/time easier. I'm planning to do is whether or not a row's data is past the current date.

Java code:

public void saveExam() {
date = (DatePicker)findViewById(R.id.examDate);
Integer day = date.getDayOfMonth();
Integer month = date.getMonth();
Integer year = date.getYear();
StringBuilder temp = new StringBuilder();
temp.append(day.toString()).append("/").append(month.toString()).append("/").append(year.toString());
String dateStr = temp.toString();

time = (TimePicker)findViewById(R.id.examTime);
Integer hour, minutes;
if (Build.VERSION.SDK_INT >= 23 ) {
hour = time.getHour();
minutes = time.getMinute();
} else {
hour = time.getCurrentHour();
minutes = time.getCurrentMinute();
}
temp = new StringBuilder();
temp.append(hour.toString()).append(":").append(minutes.toString());
String timeStr = temp.toString();

Toast.makeText(add_exam.this, dateStr + ", " + timeStr, Toast.LENGTH_LONG).show();
}

Answer

According to Sqlite documentation (section 2.2), you should store your date in one of the following ways:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

The comparison problem can be solved using the strftime function that allows you to compere dates regardeless their storage type (TEXT, REAL or INTEGER).

Let's say that you want to get all the rows in your database that have date Jenuary. You can do:

SELECT * FROM table
WHERE strftime('%m', your_date_column) == '01'

Or maybe you want get all the rows with time 09:40:

SELECT * FROM table
WHERE strftime('%H:%M', your_date_column) == '09:40'

Or also:

SELECT * FROM table
WHERE strftime('%s', your_date_column) < strftime('%s','now')

will return you all the rows with date preceding the current date (now).

SUMMARY: The function strftime abstracts the database storing mode, so it doesn't actually metter which mode you use.