Managing Date/Time Fields in C#

As explained in the Datetime FIelds page, a field of type Datetime (actually mapped to class System.DateTime) is a 64 bit integer value used to store the date and time in databases and the precision of Datetime values is determined by the runtime parameter DATETIME_PRECISION which represents the number of ticks per second. The default value for DATETIME_PRECISION is 1000, which means that the database stores a Datetime value as the number of milliseconds since the EPOCH (i.e. 00:00:00 UTC 1 January 1970).

For example , the following code snippet defines a Datetime field d and a sequence of Datetime values sd:

 
    [Persistent]
    public class T
    {
        public DateTime d;
         
        [Sequence()]
        public UnorderedSequence<DateTime> sd;
    };
     

The resolution of Datetime fields can be changed by an application via the runtime option RT_OPTION_DATETIME_PRECISION. The DATETIME_PRECISION can be set to 1 to store the number of seconds, or 1000000 (1,000,000) to store the number of microseconds. For example:

 
    Database db = new Database(config);
    db.SetRuntimeOption(Database.RT_OPTION_DATETIME_PRECISION, 1000000);
     

The following code snippet demonstrates how to store and load Datetime values using the native C# interface:

 
    T t = new T();
    t.d = new DateTime(...);
    ...
    con.Insert(t);
     
    ...
     
    Cursor<T> cursor = new Cursor<T>(con, ...);
    foreach (T t in cursor) 
    {
        Console.WriteLine(t.d);
    }
     

The following code snippet demonstrates how to store and retrieve Datetime values using SQL:

 
    DateTime d = new DateTime(...);
    DateTime sd = new DateTime[N];
    <create sd elements>
    con.ExecuteStatement("INSERT INTO T VALUES(?,?)", d, sd);
     
    ...
     
    SqlResultSet result = con.ExecuteQuery("SELECT d, sd FROM T");
    foreach (SqlTuple tuple in result) 
    {
        DateTime d = (DateTime) tuple[0];
        DateTime[] sd = new DateTime[1];
        Console.Write("d : " + d);
        using (Sequence seq = (Sequence)tuple[1]) 
        {
            while ((len = seq.Get(sd)) > 0)
            {
                Console.Write("    sd : " + sd[0]);
            }
        }
    }
    result.Dispose();