Menu
I have problems with saving DateTime to an SQL Lite data base. (perhaps also with MS SQL)
I want to save a DateTime in UTC time with NHibernate to the database and load it from the database. We work in the hole application with utc time and when we present the time on the ui, we change it to local time.
I read a lot about DateTime and NHibernate:
But nothing works. Some expample:
PreUpdate: is before saving the entity.
Saved: is the saved object after saving with nhibernate (repo.save(o);).
Loaded: when I load the entity from the repository by id.
// The next 3 examples are with:
o.Created = DateTime.UtcNow;
CustomType<TimestampType>()
UtcTime: 16:44... LocalTime: 18:44
The problem here is, when I reload the object by id, the new object has the time 18... (+2h) instead of 16.... and the DateTime kind is Unspecified.
CustomType<DateTimeType>()
UtcTime: 16:49... LocalTime: 18:49
With this solution, I loose the milliseconds and the DateTime kind is also Unspecified.
CustomType<UtcDateTimeType>()
UtcTime: 17:01... LocalTime: 19:01
With this solution, I loose the milliseconds, the DateTime kind is utc but it is the wrong time, it should be 17:01....
So another idea was to use in the application only DateTime.Now and save the utc time in the database. Some example:
// The next 3 examples are with:
o.Created = DateTime.Now;
CustomType<TimestampType>()
UtcTime: 17:21... LocalTime: 19:21
With this solution, I have the milliseconds, the DateTime kind is Unspecified and the loaded time is not utc.
CustomType<DateTimeType>()
UtcTime: 17:19... LocalTime: 19:19
With this solution, I loose the milliseconds, the DateTime kind is also Unspecified and the loaded time is not utc.
CustomType<UtcDateTimeType>()
UtcTime: 17:14... LocalTime: 19:14
With this solution, I loose the milliseconds, the DateTime kind is utc but it is the wrong time, it should be 17:14....
So I have some questions:
I've also created an own mapping:
namespace Persistence.Common.NHibernate
{
using System;
using System.Data;
using global::NHibernate.Engine;
using global::NHibernate.Type;
/// <summary>
/// This is almost the exact same type as the DateTime except it can be used
/// in the version column, stores it to the accuracy the database supports,
/// and will default to the value of DateTime.Now if the value is null.
/// </summary>
/// <remarks>
/// <p>
/// The value stored in the database depends on what your data provider is capable
/// of storing. So there is a possibility that the DateTime you save will not be
/// the same DateTime you get back when you check DateTime.Equals(DateTime) because
/// they will have their milliseconds off.
/// </p>
/// <p>
/// For example - SQL Server 2000 is only accurate to 3.33 milliseconds. So if
/// NHibernate writes a value of <c>01/01/98 23:59:59.995</c> to the Prepared Command, MsSql
/// will store it as <c>1998-01-01 23:59:59.997</c>.
/// </p>
/// <p>
/// Please review the documentation of your Database server.
/// </p>
/// </remarks>
[Serializable]
public class CustomUtcTimestampType : TimestampType
{
public CustomUtcTimestampType()
{
}
public override object Get(IDataReader rs, int index)
{
return Convert.ToDateTime(rs[index]).ToLocalTime();
}
/// <summary>
/// Sets the value of this Type in the IDbCommand.
/// </summary>
/// <param name="st">The IDbCommand to add the Type's value to.</param>
/// <param name="value">The value of the Type.</param>
/// <param name="index">The index of the IDataParameter in the IDbCommand.</param>
/// <remarks>
/// No null values will be written to the IDbCommand for this Type.
/// </remarks>
public override void Set(IDbCommand st, object value, int index)
{
DateTime dateTime = (DateTime)((value is DateTime) ? value : DateTime.UtcNow);
dateTime = DateTime.SpecifyKind(dateTime.ToUniversalTime(), DateTimeKind.Unspecified);
((IDataParameter)st.Parameters[index]).Value = dateTime;
}
public override string Name
{
get { return "CustomUtcTimestamp"; }
}
public override object FromStringValue(string xml)
{
return DateTime.Parse(xml);
}
#region IVersionType Members
public override object Seed(ISessionImplementor session)
{
if (session == null)
{
return DateTime.UtcNow;
}
return Round(DateTime.UtcNow, session.Factory.Dialect.TimestampResolutionInTicks);
}
#endregion
public object StringToObject(string xml)
{
return DateTime.Parse(xml);
}
public override string ObjectToSQLString(object value, global::NHibernate.Dialect.Dialect dialect)
{
return '\'' + value.ToString() + '\'';
}
}
}
366
UtcDateTimeType is to assume the time set is in UTC and when fetch it also consider it as UTC,If you do following quick test,
public class UtcTime
{
public virtual long Id { get; set; }
public virtual DateTime DateSaved { get; set; }
}
public class UtcTimeClassMap : ClassMap<UtcTime>
{
public UtcTimeClassMap()
{
Id(t => t.Id).GeneratedBy.Native();
Map(t => t.DateSaved ).CustomType<UtcDateTimeType>();
}
}
[Test]
public void SimpleTest()
{
long id = 0;
ISession session = _sessionFactory.OpenSession();
using (ITransaction tran = session.BeginTransaction())
{
UtcTime utc = new UtcTime();
utc.DateSaved = DateTime.Now;
session.Save(utc);
tran.Commit();
Console.WriteLine(utc.DateSaved.Ticks + "_" + utc.DateSaved.Kind + "_" + utc.Date.ToString());
id = utc.Id;
}
session.Flush();
session.Clear();
session = _sessionFactory.OpenSession();
var retrieved = session.Get<UtcTime>(id);
Console.WriteLine(retrieved.DateSaved.Ticks + "_" + retrieved.Date.Kind + "_" + retrieved.DateSaved.ToString());
}
Output
INSERT INTO [UtcTime] (DateSaved) VALUES (?); select SCOPE_IDENTITY()
635634005813892469_Local_31/03/2015 12:09:41 PM
SELECT utctime0_.Id as Id3_0_, utctime0_.DateSaved as Date3_0_ FROM [UtcTime] utctime0_ WHERE utctime0_.Id=?
635634005810000000_Utc_31/03/2015 12:09:41 PM
even though I have persisted the 12:09:41 as local, when I fetch it back it's UTC, and the same time as it assumes everything happens in UTC. This test was done with the SQLServer database.
If I repeat the same test with SQLite, output is,
INSERT INTO "UtcTime" (DateSaved) VALUES (?); select last_insert_rowid()
635634005197863939_Local_31/03/2015 12:08:39 PM
SELECT utctime0_.Id as Id3_0_, utctime0_.DateSaved as Date3_0_ FROM "UtcTime" utctime0_ WHERE utctime0_.Id=?
635634401190000000_Utc_31/03/2015 11:08:39 PM
I can see one hour unexplained difference here (since difference between local time and UTC where the test is conducted is 11 hours, this does not explain it. Only explanation I can think of is that there might be some sort of bug in the SQLLite dialect.
As a fix to this problem with precision you could add a custom type like this,
public class UTCTimeStampType : TimestampType
{
public override object Get(IDataReader rs, int index)
{
return ConvertToUtc(base.Get(rs, index));
}
public override object Get(IDataReader rs, string name)
{
return ConvertToUtc(base.Get(rs, name));
}
public override object FromStringValue(string xml)
{
return ConvertToUtc(base.FromStringValue(xml));
}
private DateTime ConvertToUtc(object value)
{
var dateTime = (DateTime) value;
return new DateTime(dateTime.Ticks).ToUniversalTime();
}
}
I was having this same problem, in my case I was trying to round-trip everything as Utc, with fields mapped as UtcDateTimeType, but times were coming back marked as UTC but converted to local time. The answer is both simple and annoying. You must tell the SQLite engine not to convert DateTimes for you by adding a parameter to the connection string:
config.DataBaseIntegration(c =>
{
c.Dialect<SQLiteDialect>();
c.Driver<SQLite20Driver>();
c.ConnectionString = "DataSource=MyAppDB.sqlite;DateTimeKind=Utc";
// ^^^^^^^^^^^^^^^^
...
After doing this my application behaved MUCH more sensibly.
2
Thank you for your answer. With your version of the custom type, I had usually a wrong time when I've loaded the datetime. The problem is this code base.Get(rs, index). When I execute this method, it converts the utc time from the database to a wrong DateTime. My solution is now, to save the time in utc, but before I change the DateTimeKind to Unspecified and store the value in the data base (SQLite). The result is a time without Z at the end. When I load the time, I change the DateTimeKind after loading the DateTime and everything works.
The following code solve my problems:
using System;
using System.Data;
using global::NHibernate.Engine;
using global::NHibernate.Type;
using Foundation.Core;
/// <summary>
/// This type save the <see cref="DateTime"/> to the database. You need to save the <see cref="DateTime"/> in UTC (<see cref="DateTimeKind.Utc"/>).
/// When you load the <see cref="DateTime"/>, then time is in UTC.
/// </summary>
/// <seealso cref="http://stackoverflow.com/questions/29352719/save-and-load-utc-datetime-with-nhibernate"/>
public class UtcTimestampType : TimestampType
{
public override string Name
{
get { return "UtcTimestamp"; }
}
/// <summary>
/// Sets the value of this Type in the IDbCommand.
/// </summary>
/// <param name="st">The IDbCommand to add the Type's value to.</param>
/// <param name="value">The value of the Type.</param>
/// <param name="index">The index of the IDataParameter in the IDbCommand.</param>
/// <remarks>
/// No null values will be written to the IDbCommand for this Type.
/// The <see cref="DateTime.Kind"/> must be <see cref="DateTimeKind.Utc"/>.
/// </remarks>
public override void Set(IDbCommand st, object value, int index)
{
DateTime dateTime = (DateTime)((value is DateTime) ? value : DateTime.UtcNow);
Check.IsValid(() => dateTime, dateTime, time => time.Kind == DateTimeKind.Utc, "You need to save the date time in the utc format.");
// Change the kind to unspecified, because when we load the datetime we have wrong values with kind utc.
((IDataParameter)st.Parameters[index]).Value = DateTime.SpecifyKind(dateTime, DateTimeKind.Unspecified);
}
public override object Get(IDataReader rs, int index)
{
return ChangeDateTimeKindToUtc(base.Get(rs, index));
}
public override object Get(IDataReader rs, string name)
{
return ChangeDateTimeKindToUtc(base.Get(rs, name));
}
public override object FromStringValue(string xml)
{
return ChangeDateTimeKindToUtc(base.FromStringValue(xml));
}
public override object Seed(ISessionImplementor session)
{
if (session == null)
{
return DateTime.UtcNow;
}
return Round(DateTime.UtcNow, session.Factory.Dialect.TimestampResolutionInTicks);
}
private DateTime ChangeDateTimeKindToUtc(object value)
{
DateTime dateTime = (DateTime)value;
return new DateTime(dateTime.Ticks, DateTimeKind.Utc);
}
}
What do you think about these 2 Methods? Do I need these? Should I use Round(DateTime.UtcNow...? When is FromStringValue needed?
public override object FromStringValue(string xml)
{
return ChangeDateTimeKindToUtc(base.FromStringValue(xml));
}
public override object Seed(ISessionImplementor session)
{
if (session == null)
{
return DateTime.UtcNow;
}
return Round(DateTime.UtcNow, session.Factory.Dialect.TimestampResolutionInTicks);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With