CODEWITHSUNDEEP
c#visual-studio-2008sqlitewindows-mobile
user153923
user153923

Reputation:

SQLite Date and Time Datatype

I am trying to build a nice, small database to run on a mobile application (Windows Mobile 5, if you are curious).

In the SQLite Documentation, the Date and Time Datatype is defined as follows:

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

So, saving my DateTime value as either a REAL (float) or INTEGER is the same size.

What about the TEXT format? There are 23 characters above in the text YYYY-MM-DD HH:MM:SS.SSS. Is that 8-bytes per character? If so, that is a HUGE waste of space to store in Text format (which is what I am currently doing).

What about the REAL format? Would I define a base date of November 24, 4714 B.C.? (I am not even sure if Visual Studio 2008 will let me do that. I've never tried.) Then get the TimeSpan between base date and date I want, extract the number of days, and store that?

// is this how to declare this date?
private static readonly DateTime nov24_4714bc = new DateTime(-4714, 11, 24);

public static double GetRealDate(DateTime dateTime) {
  // FYI: subtracting dates in .NET returns a time span object
  return (dateTime - nov24_4714bc).TotalDays;
}

What about the INTEGER format? Would I define a base date of 1970-01-01 00:00:00 UTC (please tell me how to do that!), then get the TimeSpan between base date and my input date, extract the number of seconds, and store that?

// is this a UTC date?
private static readonly DateTime utc1970_01_01 = new DateTime(1970, 1, 1);

public static double GetIntDate(DateTime dateTime) {
  // FYI: subtracting dates in .NET returns a time span object
  return (dateTime - nov24_4714bc).TotalSeconds;
}

Any help with this? I am a little confused on a few points.

Upvotes: 4

Views: 6400

Answers (1)

dan04
dan04

Reputation: 90995

  • Use the TEXT format if "human-readability" is important.
  • Use one of the numeric formats if saving space is important.

If you don't need millisecond precision, you can save space in the TEXT format by only including the part you do need. There are 3 shorter formats accepted by SQLite date/time functions:

  • YYYY-MM-DD HH:MM:SS (19 characters)
  • YYYY-MM-DD HH:MM (16 characters)
  • YYYY-MM-DD (10 characters)

(NEVER use MM/DD/YYYY; it's not supported, and it doesn't sort correctly.)

Would I define a base date of November 24, 4714 B.C.? (I am not even sure if Visual Studio 2008 will let me do that. I've never tried.)

You can't: System.DateTime only supports the years 1 to 9999. You need to pick a different base date, and then do (dateTime - baseDate).TotalDays + baseDateJD, where baseDateJD is the Julian date of the base date. Some reasonable choices are:

  • 0001-01-01 = JD 1721425.5
  • 1970-01-01 = JD 2440587.5
  • 2000-01-01 = JD 2451544.5

Upvotes: 2

Related Questions