Time zone issue with Tridion database

Question

I have a situation, where I need to check if the file in the web server is older than the last published time in the broker. But the problem is the presentation farm and Tridion servers are not in the same time zone.

I found an issue when I tried to update a binary item shortly after it was initially published. If I published at 12pm cms time (PST) and a presentation server grabbed the item, it had a modification time 12pm in the broker but a local modification time in presentation server of 3pm (3 hours time difference as it is in EST). If the same binary item was changed and published an hour later, the time is updated in the broker to 1pm, but the code ignores the new asset since it is earlier than 3pm (the local modification time).

As a solution, currently I am updating the last modified time of the file with the last published time from broker, every time I am updating the file.

But, can we force Tridion to store the UTC time instead of local time in both CMS & broker DB? or is there any better solution available for the above scenario.

Answer 1

As you've tagged this DD4T this is likely related to the BinaryFileManager class in the DD4T project. I see in the check to ProcessRequest DateTime default is used. Given a DateTime value can reflect only UTC and the local time zone's time I'd suggest you investigate the possibility of using a DateTimeOffset value to reflect the time in a particular time zone.

Perhaps along the lines of

lastPublishedDate = new DateTimeOffset(lastPublishedDate, new TimeSpan(deliveryServerHoursOffset, deliveryServerMinutesOffset, 0));

Remember

• offsets are not always an integer of hours (for example, Indian Standard Time is UTC+05:30)
• convert to local times at the last possible moment (in case the hour/minute switches during your processing)
• time zones and offsets are not fixed and may change
• ensure the hardware and OS clocks are set to UTC on all servers (on FAT32 this is not the case by default)

Answer 2

With a multiple server setup, it is always good practice to use UTC as your only timezone to avoid any confusion.

Other than that I believe Tridion is indeed just using local server times for things like modification date and only for scheduled tasks it will convert to UTC (to prevent another server from picking up something too early/late).

Which means that if you would set all your server timezones to UTC, then everything should be equal, although all your current modification dates in the databases might be off.

Answer 3

There was some business objection to update the timezone of the servers. So I implemented the following solution

We can overcome this issue if we update the file modification time (web server) with the last published time from broker. More in the following way the optional use of the web configuration file might not require and a generic code base can be used for all the projects.

The following changes require to be done in BinaryFileManager.cs -->ProcessRequest

DateTime? serverPublishTime = null;

………….

DateTime lpb = BinaryFactory.FindLastPublishedDate(urlPath);

serverPublishTime = lpb;

……..

bool fileCreatedorModifed = WriteBinaryToFile(binary, physicalPath, dimensions); if (fileCreatedorModifed && serverPublishTime != null) if (serverPublishTime.Value != DateTime.MinValue.AddSeconds(1)) File.SetLastWriteTime(physicalPath, serverPublishTime.Value); return fileCreatedorModifed;