What is the appropriate date formatting so the lexicographic ordering is the same as time ordering?

Question

In my SQLite database, there is no Date datatype, so I have to store timestamps in text format.

Does the format yyyy-MM-dd HH:mm result in the correct ordering such that when you sort it lexicographically (by doing a normal sort ASC or DESC), it also orders by time value inherently?

Answer 1

Your format is the right idea; when sorted alphabetically it is also chronological.

You can take a step further, for a better version of that format, a standard format, to make your work simpler and easier.

ISO 8601

The ISO 8601 standard defines a variety of practical sensible formats for text representing date-time related values.

For a date and time combined the format is:

YYYY-MM-DDTHH:MM:SS.SZ

For example:

2016-04-28T18:22:20.123Z

This format as a string sorts chronologically as you need.

The T in the middle separates the Date portion from the Time portion. The Z on the end is short for Zulu which means UTC.

Generally, best practice is to convert your date-time values to UTC for storage and database. Your JDBC driver likely does that for you but I don't know about SQLite.

java.time

The java.time framework is built into Java 8 and later. Much of that functionality is back-ported to Java 6 & 7 in the ThreeTen-Backport project, and further adapted to Android in the ThreeTenABP project.

These new classes supplant the old java.util.Date/.Calendar and related classes that have proven to be poorly designed and troublesome.

These classes use ISO 8601 formats by default when parsing/generating textual representations of date-time values. Search Stack Overflow for many examples.

An Instant is a moment on the timeline in UTC with a resolution of nanoseconds.

Instant instant = Instant.now();

Simply call toString to generate a String representation of that value.

String stringForDatabase = instant.toString();

In Java 8 the current moment is captured to only milliseconds resolution due to legacy implementation of the Clock interface, for 3 decimal places for the fraction of a second. For example, 2016-04-29T00:12:57.123Z. In Java 9 and later has a modern implementation of Clock, able to capture the current moment in up to 9 decimal places (nanoseconds) as far as is supported by your computer’s hardware clock.

The default formatter used by Instant:toString prints the fraction of a second with 0, 3, 6, or 9 digits, as many as needed to represent the non-zero portion of the fraction of a second. All of these sort alphabetically & chronologically as requested, so you could store any of these in your database.

• 2016-04-29T00:12:57Z
• 2016-04-29T00:12:57.123Z
• 2016-04-29T00:12:57.123456Z
• 2016-04-29T00:12:57.123456789Z

These all parse directly back into an Instant instance. So no need to bother with defining your own formatting pattern as in the Question.

Instant instant = Instant.parse( "2016-04-29T00:12:57.123456789Z" );

To see the wall-clock time for a particular place, apply a time zone (ZoneId) to get a ZonedDateTime.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );

Extract an Instant for storage back into the database.

Instant instant = zdt.toInstant();
String forDatabase = instant.toString();

Answer 2

Yes. Just that you should not miss any 0's in between. ('04' for April).

It's necessary to represent April as '04', not just '4'.