This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Can you give me some guidance concerning storing date/time fiels in the db in UTC? I'd like to store all date/times in utc but have them auto converted to localtime when reading and converted to utc when written. Is there some automated way to do this? I saw the AutoTimestampMode enum for the createdOn etc columns but is there something similar with userdefined columns? Thanks Peter |
|
|
We don't perform any conversion on DateTime columns (the AutoTimestampMode is just telling us what timestamps to generate, not performing any transformation on the stored data), but you can easily implement this by taking advantage of LightSpeed's distinction between the persistence model and object API model. The persistence model is represented by fields, and the API by properties. Normally fields are mapped very directly to properties, but they don't have to. So you could implement a date/time member like this: private DateTime _lastActive; // storage model: UTC // API: get and set using local time Consumers of your object interact with it using local time; but what LightSpeed stores is the field, which you maintain in UTC. Note that if you're using the designer, you'll need to change the property's Generation setting to FieldOnly, and implement the property by hand in the partial class. I realise this could be a bit tedious if you've got a lot of properties that you want to customise in this way: there is a way to automate it but it's a bit complicated. Let me know if this is important to you. |
|
|
If there is a way to automate the process that would be great. If the above method was the only one, at least we don't have to convert every where we use it. Peter |
|
|
Okay, here is an outline of how to automate this. Please be warned, this is fairly ninja stuff and you may decide that updating the properties by hand is easier! The extensibility model revolves around two things: extension properties and custom templates. For information about extension properties, see http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=1956&PostID=5101. For information about custom templates, see http://www.mindscape.co.nz/blog/index.php/2009/09/16/customising-lightspeed-entity-templates/. How would we put these together in your scenario? First, define an extension property GenerateUtcLocalTranslation, of type System.Boolean, with Extends = Property. Second, on each property that you want to perform UTC/local translation, set this extension property to true (it should appear in the Properties window just like any other option). Third, go into the FieldProperties.vm template and locate the property get/set implementation. Put a #if / #else / #end block around it, with the standard implementation in the #else block. For the #if clause, put $field.HasExtendedProperty("GenerateUtcLocalTranslation") (and you should probably check the value -- see the linked forum post for how). Now copy the standard implementation into the #if block and edit it to add the local/universal translation. So you will end up with something that looks like this: $Translator.TranslateMethodAttributes($field.PropertyAttributes) $Translator.TranslateType($field.DataType, $field.AllowNulls) $field.Name Please note I have not tested this code: I'm providing it as guidance rather than a finished sample. As warned in the linked blog post, forking the templates does have a maintenance cost. So please do consider the relative costs and risks of the manual property coding route before going down the automation route. |
|
|
Just to state the obvious: if you want to perform UTC/local translation for *all* DateTime properties, no exceptions, then you don't need to bother with the extension property: you can instead just check the $field.DataType in your custom template. |
|
|
Brilliant thread! I wish I was sooner aware of some things said here. Ivan, you should write us more "ninja stuff" like these :) |
|