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

Question

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 1

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.

Answer 2

tl;dr

Use:

• Strictly-defined ISO 8601 string format
• Adjusted into UTC
• T in the middle (no SPACE characters)
• Z on the end:

YYYY-MM-DDTHH:MM:SS.S…Z

2016-09-17T18:28:27Z

Strict ISO 8601 format

The Answer by GVillani82 is correct and should be accepted. The only problem is quoting the SQLite documentation that incorrectly states:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")

Two points wrong in that statement: the SPACE in middle, and three digits in decimal fraction.

T in middle

That format with a SPACE in the middle is not, strictly speaking, ISO 8601 compliant. The ISO 8601 standard requires a T in the middle rather than the SPACE. To quote the Wikipedia page summarizing the ISO 8601 standard:

…concatenating a complete date expression, the letter T as a delimiter, and a valid time expression. For example, "2007-04-05T14:30".

The T may be replaced with a SPACE only under special arrangement by both sender and recipient of the data.

Further confusing things is that the format with the SPACE in the middle does comply with the SQL standard’s definition of a date-time format. The SQL spec predates ISO 8601. The ISO 8601 is rapidly becoming the go-to format, as it continues to be adopted worldwide in the business world as well as modern computer protocols.

Decimal fraction

The ISO 8601 standard allows any number of digits in the decimal fraction of a second. The quoted doc implies three digits are required, but actually any number is allowed including zero (no digits at all, whole second).

Three digits represents milliseconds, six is microseconds, and nine is nanoseconds. Those are all commonly used in mainstream business-oriented apps. Any finer fractions would only be seen in science/engineering type of apps.

The java.time classes use nanosecond resolution, in contrast to the old date-time Java classes restricted to only milliseconds.

Practicalities

These two issues are not mere academic points, but raise practical issues.

In a Java environment, we are likely to extract this string from SQLite and then pares it as a java.time object. The java.time classes are the modern way to handle date-time programming. This framework is inspired by Joda-Time (same man leading the effort, Stephen Colbourne), defined by JSR 310, built into Java 8 and later, officially supplants the troublesome old date-time classes, back-ported to Java 6 & 7 in the ThreeTen-Backport project and further adapted to Android in the ThreeTenABP project.

The java.time classes use standard ISO 8601 formats by default when parsing/generating Strings that represent date-time values. But java.time uses the strict definitions only. This means a T in the middle of a date-time moment.

Instant instant = Instant.parse( "2016-09-17T18:28:27Z" );

The problem is that parsing the format mislabeled as ISO 8601 by the SQLite doc with a SPACE in the middle will fail to be parsed by java.time. Going the other direction, the strings generated by java.time by default will have the T in the middle rather than the SPACE.

The solution is easy: Just use the strict ISO 8601 definition, with T in the middle.

If you read deep enough into the SQLite documentation page on date-time functions you see it mentioned that:

…the "T" is a literal character separating the date and the time, as required by ISO-8601

This format with T is supported by SQLite and its date-time functions. Furthermore the doc states that the time zone indicator of Z for Zulu UTC is also supported.

…may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op.

So I suggest the wisest approach for storing a moment in SQLite is being adjusted into UTC and using the strict ISO 8601 format including the Z at the end: 2016-09-17T18:28:27Z

As for fractional seconds, the SQLite doc says that while only the first three decimal fraction digits have significance to its SQLite functions, you can actually store any number of digits. So maximum of 9 digits for the nanoseconds allowed in java.time apparently can be stored successfully though not compared precisely: 2016-09-17T18:28:27.123456789Z

If this comparison precision is a problem for your app, you could either do the comparison work on the Java side in your app using java.time classes that can handle nanoseconds, or you could consider truncating your date-time values to milliseconds resolution. The java.time classes provide a convenient truncation method.

Other options

What about the options?

• REAL as Julian day numbers.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Floating point technology used for real numbers, such as float/Float & double/Double in Java, purposefully trades away accuracy for speed of execution. This means extraneous digits often appear on the right of your fraction. While this may be tolerable in some science/engineering/gaming arenas, it is not acceptable in matters of money nor in some uses of date-time values. So, not a good idea in my opinion.

A count-from-epoch such as number of seconds since the beginning of 1970 in UTC is not a good idea. While the java.time classes use this approach under the covers to actually track time, we have those covers for a reason. Such integer numbers have no meaning to a human reader, no date-time value can be discerned, so bugs may go undetected. The data type itself is ambiguous as we must guess at the intended granularity with some computer systems using whole seconds as a count, others use milliseconds or microseconds or nanoseconds or still other granularities. As for the epoch, that too is ambiguous as at least a couple dozen epoch points have been used in various computing systems.

Other databases

Be aware that SQLite was never intended to be a serious heavy-duty database. Hence, the lite in SQLite. The inventor has explained that he intended SQLite to be a step-up alternative to writing plain text files. He never intended SQLite to be a competitor to more serious databases.

So if you are running into pain points with the limitations of SQLite, your needs may have outgrown the design parameters of SQLite. If you need a more serious database, use a more serious database.

In the Android environment, my first consideration would be the H2 Database Engine. This pure-Java, open-source, free-of-cost database product is actively developed and growing in popularity. See:

• H2 documentation about use on Android
• H2 Database vs SQLite on Android - Stack Overflow