System.Data.SQLite

 Hi,

 I am currently working on a .NET version of a Mac application which utilizes SQLite as the data store. CoreData saves dates in the SQLite db as unix timestamps with 1/1/2001 GMT as the epoch instead of 1/1/1970.

In this table I have a table like this:

CREATE TABLE ZTABLE ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZTYPE INTEGER, ZACTIVE INTEGER, ZNOTE VARCHAR, ZDATE TIMESTAMP);

Any idea how I would proceed with reading the timestamp value from the ZDATE column?

 EDIT: I solved this by modifying the provider to support a third datetimeformat. In case anyone is interested, here is the code I added/modified:

In SQLiteConvert.cs:

static DateTime ConvertFromCoreDataTimestamp(string timestamp)
{
CultureInfo cultureInfo = new CultureInfo("en-US"); // Use the correct decimal delimiter ( . ) when parsing to/from string
DateTime origin = new DateTime(2001, 1, 1, 0, 0, 0, DateTimeKind.Utc);
return origin.AddSeconds(double.Parse(timestamp, cultureInfo)).ToLocalTime();
}

static string ConvertToCoreDataTimestamp(DateTime date)
{
DateTime origin = new DateTime(2001, 1, 1, 0, 0, 0, DateTimeKind.Utc);
TimeSpan diff = date.ToUniversalTime() - origin;
double doubleFormat = diff.TotalSeconds;
CultureInfo cultureInfo = new CultureInfo("en-US"); // Use the correct decimal delimiter ( . ) when parsing to/from string
return doubleFormat.ToString(cultureInfo);
}

public DateTime ToDateTime(string dateText)
{
switch (_datetimeFormat)
{
case SQLiteDateFormats.Ticks:
return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
case SQLiteDateFormats.CoreData:
return ConvertFromCoreDataTimestamp(dateText);
default:
return DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
}
}

public string ToString(DateTime dateValue)
{
switch (_datetimeFormat)
{
case SQLiteDateFormats.Ticks:
return dateValue.Ticks.ToString(CultureInfo.InvariantCulture);
case SQLiteDateFormats.CoreData:
return ConvertToCoreDataTimestamp(dateValue);
default:
return dateValue.ToString(_datetimeFormats[0], CultureInfo.InvariantCulture);
}
}

In SQLiteConnection.cs:

Changed:

SQLiteDateFormats dateFormat = String.Compare(FindKey(opts, "DateTimeFormat", "ISO8601"), "ticks", true, CultureInfo.InvariantCulture) == 0 ? SQLiteDateFormats.Ticks : SQLiteDateFormats.ISO8601;

To:

SQLiteDateFormats dateFormat;
string dFormat = FindKey(opts, "DateTimeFormat", "ISO8601");
switch (dFormat)
{
case "ticks":
dateFormat = SQLiteDateFormats.Ticks;
break;
case "CoreData":
dateFormat = SQLiteDateFormats.CoreData;
break;
default:
dateFormat = SQLiteDateFormats.ISO8601;
break;
}

You might consider writing a user function to convert the timestamp to a standard SQLite format. For select purposes you could hide the funtion in a view if desirable. Here is a sample function to extract the month from a date:

  /// <summary>MONTH function for SQLite</summary>
 [SQLiteFunction(Name = "MONTH", Arguments = 1, FuncType = FunctionType.Scalar)]
 public class SQLiteMonth : SQLiteFunction
 {
  /// <summary>MONTH function implementation</summary>
  /// <param name="args">The datetime value.</param>
  /// <returns>The month of the datetime argument</returns>
  public override object Invoke(object[] args)
  {
   DateTime dt;
   if ( args[0] is long )
    dt = new DateTime((long)args[0]);
   else
    dt = SQLiteConvert.ToDateTime(Convert.ToString(args[0]));
   return dt.Month;
  }
 }

I am sorry for the delayed response to your reply.

The problem is that I am not able to read any data from the timestamp columns in the db at all. My current idea is to add this format to the system.data.sqlite library, but so far I haven't succeeded in doing that. Any idea where this could be added? Is it just in SQLiteConvert.cs or are there any other places which should be updated too?
 

It's been a while, but I am now back at this problem. It seems like I misunderstood what you meant with 'user function' the last time and I see now what you meant and what a user function in system.data.sqlite is. I am not sure if this would help me though. The reason for that is that I am using NHibernate to interact with the database and have, as far as I know, little impact on the way the queries are constructed so I am not sure how to actually make NHibernate utilize a user function. That is why I initially wanted to create a new datetimeformat in system.data.sqlite and use that, an alternative would be to have system.data.sqlite simply return the string value in the timestamp columns. This is an example date saved in the database by CoreData on OSX: 198706162.143747

Are there _any_ way to read such values (ex. 198706162.143747) from a timestamp column with this wrapper without altering the source, considering that it is not among the recognized datetime formats and not transferable to a long value (Ticks)?