Hi!

I'm using a TimeSpan property (Nullable) to map a Time field from MySQL.

It looks like this:



That's right, ok?

But when I call the Save method from ISession, the SQL generated is:




So, I have some doubts:

1) Is it correct to map Time fields using TimeSpan?
2) What should I do to this code works?

Thanks!

The behaviour you reported is expected as the TimeSpan class is persisted using the tick-count value of the object (100-nanoseconds). If you are not comfortable storing data at this granularity, then you may want to look at nHibernate's IUserType interface.

The IUserType interface allows you to describe how types are persisted; you will need to create an IUserType for TimeSpan and have it persist at a higher granularity (e.g. milliseconds, seconds).

Ok, but how do you map your TIME fields?

Thanks

If you are trying to map the date and/or time of day, use the DateTime class.

Hum, I think this doesn't work. I'm using the following:

- Time fields (Mysql DB)
- XML and CS mapping using DateTime type to map the fields

When I run the app, I get the following error:



So, the best solution is to create a new type that implements IUserType or do you think I can try another way?

Thanks man!

I have just another question:

Why nhibernate maps a TimeSpan property to an int column? Is it a best pratice to save time values into int fields? I didn't understand.

I've noticed this after look the NH source code:



Thanks again.

Pretty much, yes.

If you store DateTime as raw text, it can consume up to 17-18 bytes if you want millisecond precision. If you use "ticks" (an internal representation where 1 tick = 1000 ms), you can store a large range of values in only 8 bytes.

Internally, MS products (can't speak for *nix) have used 8 bit integer tick counts for representing time values for some time. The ease of doing date arithmetic with this format is obvious - you only have to make sure you have robust conversion routines, and do integer arithmetic, rather than worrying about seperate math routines for different calendar systems etc.

It's also rather more compact than using a string representation, so many database systems will use this method. One notable exception I've discovered recently is SQLite, which by default uses ISO 8601 strings to represent dates.

In the case of TimeSpan, this is an expression of magnitude, and not a discrete Time value. In particular, it makes no sense in a DateTime field, because it can hold negative values which would break the unsigned integer representation used. You could format it as an ISO 8601 duration string, but that again would be more bulky.

If you want Time, the best way is to use the DateTime struct and ignore the Date component, or to write your own Time struct. SQLite wins here in terms of storage space ; because it uses ISO8601 it can represent times down to the second in 7 bytes or less (6 chars plus one byte overhead).

Thanks man! I was thinking about it and the Delphi (object-pascal) Language also uses this kind of representation (it uses the Double type).

I still with the same problem. :(

When I try to store a time (hh:mm) into an int (Int32) property, the .NET raises the following exception:



The value is 199800000000.

So, I've changed the property type to Int64. Now, it's possible to store 199800000000 into Int64 properties, but the database doesn't accept this. The value 199800000000 is saved as 2147483647. I don't want to use a bigint type.

Another problem is when I try to access these values using Delphi DataSets. I think they do not represent the Time/DateTime fields using this precision. I get the following exception:



Whay should I do? Thanks again.

Delphi DateTime is (at least in win32) a floating point value. Maybe You should try double as database type?

Gert

Hi!

I've tryied this, but I get an Exception when I bind the DataField with the Control:



This test was done using Delphi.

By the way, this field (TIME) will be accessed by a .NET app and a Delphi app.

Thanks

Did I understand correctly that the Delphi for Win32 is used?

Anyway, have You tried to create persistent fields in Delphi TDataSet? And set the field's data type to datetime (or whatever is correct, I haven't used delphi for a while...)

Gert

Yes! Delphi for Win32 and VS 2005 with .NET 2.0.



Yes, I've tried. But I have some controls that I need to bind with the DataSet. The controls are DateTimePickers, and if I bind with a field type different of its (DateTime), it raises and Exception.

:(

I'll correct an order-of-magnitude error ; ticks are 10,000 per millsecond, not 1,000.

It's not all cherry pie ; if you want variable precision - and the ability to express the level of precision you are using - using integers doesn't help out so much. Variable precision is a surprisingly common requirement (esp. in medical systems, where I earn my crust), but I've yet to see an RDBMS or native type system that deals with it (half-assed exception coming up).

Because all integer based DateTime values respresent a 1/10,000 millisecond precise time, you can't tell whether 2004-03-02 is distinct from 2004-04-02 00:00 or not - you can't tell whether the original data meant "second of April, whenever dude", or "EXACTLY MIDNIGHT on the second of April or people DIE".

Probably going a bit offtopic at this point :-)

This is one advantage that string representations of date have. The downside is as mentioned, the bulk. I poked around in a SQLite DB with a text-editor, and it turns out that if you feed it native DateTime via the ADO.NET provider, it stores it as full-precision ISO 8601 with punctuation down to the tick precision level ; 27 bytes of data! You can set it to use integer ticks though.

Hmm, posted the above after editing it on the train and getting back into work from the weekend, so it might not be strictly relevant to the rest of the thread since then...

Ok, questions...

Can Delphi bind objects to UI controls instead of just datasets? In which case you could expose the property as the correct type without worrying about what database type it's using.




This isn't surpising ; this value is not going to fit in a 32-bit integer.

If you are ONLY using HH:mm values, perhaps you could consider using strings...

I'm not really familiar enough with Delphi datasets to advise you about them though.

It seems though, that some of the problem stems from using "Time" values, which I presume in the .NET stuff are being represented as TimeSpan structs. It is an additional piece of trouble, but perhaps if you chose to represent these time values using DateTime structs in .NET, and use native MySQL Date/Time columns, your problems would vanish, the only downside being you'd have to code around the "0001-01-01" date attached to your time values, but I presume that setting a format string in the DTPicker will fix that.