Storing a calendar day in sqlite database

Question

I need to store items with a calendar date (just the day, no time) in a sqlite database. What's the best way to represent the date in the column? Julian days and unix seconds come to mind as reasonable alternatives. If I go with a unit other than days at what clock time should it be?

Update: I am aware of ISO8601 and actually used it to store the date as a string in YYYY-MM-DD format for the prototype. But for various arithmetic I have to convert it to some number internally, so I'd prefer to store a number and just convert to string for display. What units should this number be in, with what origin, and if the units is more precise than days what time of day should be used?

Answer 1

If you expect to be handing the date to an external tool or library, you should use whatever format it expects. Unix time seems to be the lingua franca of digital horography, so it's a good default if external circumstances don't make the choice for you.

ISO 8601 may be of interest.

Edit:

But for various arithmetic I have to convert it to some number internally, so I'd prefer to store a number and just convert to string for display.

Ah, that makes sense. If your environment (.NET? Python? C++?) has time-handling tools, it would be best to use their native unit and epoch. There's no reason to re-write all those date-manipulation functions; they're trickier than they look. Otherwise, I'd use days in the local (Gregorian?) calendar since a reasonable epoch for your application. Be generous, you don't want a reverse Y2K bug when you suddenly need to handle a date earlier than you ever expected.

The time of day is entirely a matter of taste. Midnight seems like the cleanest choice (all zeros in the hour, minute, second fields), but since it never reaches the user there's no practical implications. It would be a good spot for an easter egg, in fact.

Answer 2

If you want to cover your bases make sure you store the date in utc and pick an iso standard. This approach requires minimal effort and will protect your code from future Interop headaches. I agree with @skymt ISO 8601 is a good choice.

Answer 3

If you are using C++, then boost::date_time is well worth a look.

Answer 4

"Best" almost entirely depends on where the dates come from (NSDate objects, some XML feed) and how they are manipulated (simply stored as-is vs. doing some arithmetic like "days until").

I'm not necessarily recommending this, but I wrote an app which also needed to store date-but-not-time in an SQLite DB. At first I used two columns, Month and Day, where Month was defined as the number of months since January 2000. I needed to enforce one-row-per-date, so I defined a UNIQUE index across those two columns, and it turned out to make updates horribly slow.

For my second attempt, I used a similar scheme but encoded the date into one number, by using the bottom 5 bits for the Day and the remaining upper bits for the Month (again, since Jan 2000). The conversion functions were:

Date = (Month << 5) | Day
Month = Date >> 5
Day = 0x1F & Date

This scheme preserves numerical order on dates and makes it easy to split them into components. It made sense for my app, because I was storing data in monthly chunks. If, given a Date, you want to find the next Date, this scheme might not be the best, since the Date + 1 may not be a valid date.