Store UTC date and time in the database

By default, the Optimizely Content Management System (CMS) uses Coordinated Universal Time (UTC) to store data and times in the database. One advantage of this is that you can change the web server’s time zone or move the database between time zones.

After switching to UTC in the database, API calls return local time before the switch continues to return local time. The main difference is that they are converted to and from UTC before being stored in and read from the database.

There are two ways to migrate to UTC in the database: using Visual Studio or running the stand-alone Power Shell Script. The code executes the same way in both cases, but the cmdlet in the Package Manager Console is a convenient way to start the migration from Visual Studio.

The migration script converts datetime columns in the database. This process should take about a minute on a small site on a fast database server and up to 30-60 minutes on a large site on a slower database server. You can run the script on a running site if you restart the site after the migration is complete. Any dates saved are incorrect during migration, so you should not change that content now. The migration script places locks on tables during migration, so the performance of a site that uses a lot of SQL queries is negatively affected, and the process might slow down both the migration and the site. If possible, run the migration while keeping the site offline or during low-traffic periods.

Make sure you back up the database before running the script. If the migration script fails, you can restart it and continue the batch where it was stopped but cannot roll back the complete operation. No changes to site configuration are required.

Any custom Optimizely Dynamic Data Stores (DDS) are not automatically converted. See date and time handling in DDSdate and time handling in DDS.

This option requires that the site is opened in Visual Studio and uses the connection string EPiServerDB in web.config. To get help with the cmdlet, run get-help Convert-EPiDatabaseToUtc –full. Open the project and the Package Manager Console to run the cmdlet as in the examples below.

PM> Convert-EPiDatabaseToUtc

PM> Convert-EPiDatabaseToUtc -timeZone:([TimeZoneInfo]\::FindSystemTimeZoneById("US Eastern Standard Time"))

PM> Convert-EPiDatabaseToUtc –onlySwitchToUtc:$true

PM> Convert-EPiDatabaseToUtc –onlySwitchToUtc:$true

The ConvertDatabaseToUtc.psm1 Power Shell script file is shipped in the NuGet package EPiServer.Framework but does not require Visual Studio to run. In development, you can find the file in the solution root (for example, packages/EPiServer.Framework.9.0.0/tools/ConvertDatabaseToUtc.psm1). You can run the ConvertEPiDatabaseToUtc function with the appropriate connection string and local time zone.

Start the Windows Power Shell console and run:

PM> Import-Module <path to ConvertDatabaseToUtc.psm1> PM> ConvertEPiDatabaseToUtc –connectionString "The connection string"

The options for this function are identical to those for the cmdlet, so the same scenarios apply. When running inside Visual Studio, it is a cmdlet (Convert-EPiDatabaseToUtc), but outside VS, it is a function (ConvertEPiDatabaseToUtc).