type=Timestamp in NHibernate

Recently, I was working on a user story that required recording login events to the database. The pieces of information that I had to audit were related to user such as userName, userId and login events such as event type, timestamp. Seems a pretty easy thing to do! Right? It is, except I came across an interesting issue, with a trivial solution, of course!

Problem:

I had to record the timestamp at which the login event occurred. The datatype of the database table was set to timestamp(6).

Image

This should provide a fractional seconds precision as per this. So I was expecting my login timestamps to be recorded with milliseconds precision. But I kept getting 000000 in the milliseconds portion. As you can imagine, I was like Argghh!!

Image

Go Fishing:

Now I had to go fishing for the solution. I went back to my C# code, the portion where the object for login audit was saved to the database. The timestamp was simply set to System.DateTime.Now.

Image

The issue was not resolved even after trying various formatting methods.

Solution:

After hours of banging my head and googling (of course!), I found the solution. Thanks to NHibernate, the ORM that I was using then. The fix was just adding the “type=Timestamp” attribute to the EventTime field in the mapping file (.hbm.xml).

Image

And now, thanks to NHibernate documentation, I am one happy person looking at my login audits. 🙂

Image

Happy Coding!!! 🙂