9

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?

user6261756
  • 147
  • 1
  • 7
  • added tags to post – user6261756 Apr 28 '16 at 18:18
  • 2
    You could store them as INTEGER type as milliseconds. – OneCricketeer Apr 28 '16 at 18:29
  • 1
    @cricket_007 I considered that too but could not figure out how to easily convert back and forth between human-readable dates and SQL-friendly millisecond counts. I also didn't know if one used up way more space than the other, or if one was more efficient than the other – user6261756 Apr 28 '16 at 18:30
  • It's called an epoch timestamp. Please see [how to convert](http://stackoverflow.com/questions/9754600/converting-epoch-time-to-date-string) using SampleDateFormat. – OneCricketeer Apr 28 '16 at 18:31
  • I also didn't know how to get the current time in milliseconds correctly, either – user6261756 Apr 28 '16 at 18:32
  • `Date.getTime()` will return that value – OneCricketeer Apr 28 '16 at 18:33
  • @cricket_007 Yeah I did see that link before. The whole thing is terribly confusing to me. Too many unanswered questions for me on that page. – user6261756 Apr 28 '16 at 18:34
  • The ultimate decision is up to you. The most universal storage of time is seconds since epoch. Then, you can use most Datetime libraries to convert to some Date object. Similarly, you can use libraries to format a Date object into a human-readable string, but you really only need to do that for displaying the data, you don't have to store it that way. – OneCricketeer Apr 28 '16 at 18:35
  • Oh, I didn't know that... so let's say I do long epochTime = Date.getTime(), this gives me the milliseconds, and I can use this as the value I store in the database? If I want to convert it to human-readable, then I can do SimpleDateFormat someFormat = new SimpleDateFormat("whatever format I choose"); and then String humanReadableTimestamp = someFormat.format(epochTime);? – user6261756 Apr 28 '16 at 18:47
  • That looks correct to me. You could also just store your times in `YYYY-MM-dd HH:mm`. Like I said, that's up to you. Either way is sortable. – OneCricketeer Apr 28 '16 at 18:50
  • So if I wanted the milliseconds from a Calendar, I'd do `myCalendar.getTime().getTime()`? – user6261756 Apr 28 '16 at 18:56
  • Actually, the shortcut would be `myCalendar.getTimeInMillis()` – OneCricketeer Apr 28 '16 at 18:58
  • I'll use your shortcut instead, but for my own understanding, is it the same as getTime().getTime()? – user6261756 Apr 28 '16 at 18:59
  • The callstack is longer because `getTime` returns a `Date` object, but, yes, they are equivalent. – OneCricketeer Apr 28 '16 at 19:04

2 Answers2

6

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();
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Down-voter, please leave a criticism along with your down-vote. – Basil Bourque Apr 28 '16 at 23:15
  • The default formatter for Instant has a fatal flaw: it omits fractional parts when they are zero. Because Z sorts after the digits, a time which happens to have zero milliseconds will sort after a time with the same seconds and nonzero milliseconds, and the same for microseconds. As a concrete example, consider 2020-11-19T06:10:07.198Z and 2020-11-19T06:10:07.198466Z - the former is earlier, but sorts after. – Tom Anderson Nov 19 '20 at 11:20
1

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'.

Priya
  • 59
  • 4