UTC datetime

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

UTC datetime

Garrett Young
Is there a way to force UTC in all of the datetime(2) values? This would make the data portable across timezones.
Reply | Threaded
Open this post in threaded view
|

Re: UTC datetime

roenbaeck
Administrator
I actually don't know, but looking at the specification of the datetimeoffset data type it looks like it might do what you want? Follow the link below for the specification:

http://msdn.microsoft.com/en-us/library/bb630289.aspx
Reply | Threaded
Open this post in threaded view
|

Re: UTC datetime

roenbaeck
Administrator
In the code that sets the default value (@now) of ChangedAt, RecordedAt, and ErasedAt the chronon used is, however, a datetime2(7). This can be changed on a local installation of the tool, by modifying the file SQLServer.xsl and the following section at the beginning of the file:

<xsl:variable name="chronon">
    <xsl:value-of select="'DATETIME2(7)'"/>
</xsl:variable>

<xsl:variable name="now">
    <xsl:value-of select="concat('DECLARE @now ', $chronon, ' = SYSDATETIME();')"/>
</xsl:variable>

I haven't worked with datetimeoffset, so in the best case it may work just by specifying your default values in the tool to datetimeoffset, but in the worst case you may need to change the chronon and perhaps also how @now is set to include the timezone of SYSDATETIME().
Reply | Threaded
Open this post in threaded view
|

Re: UTC datetime

Garrett Young
I do not want to make a special 'local' version of the modeler.  Your reply seems to suggest it may be as easy adding a setting in the modeler to select local or UTC times. Then, change the sqlserver.xsl file to use SYSUTCDATETIME() instead of SYSDATETIME() for the default value of @now. Agnostic timezone is very important when event times need to be communicated across timezones (think cloud computing).
Reply | Threaded
Open this post in threaded view
|

Re: UTC datetime

roenbaeck
Administrator
Great idea! I will add this in the next version of the tool (that I release to test). I will post here once I have added it.
Reply | Threaded
Open this post in threaded view
|

Re: UTC datetime

roenbaeck
Administrator
In reply to this post by Garrett Young
This fix should now be present in test.

http://www.anchormodeling.com/modeler/test