Upgrading Data from TimestampUtc to Timestamp¶

Background¶

The TimestampUtc data type has been replaced with the Known Time Zone concept, simplifying the migration process from functionalities unaware of the time zone.

While the TimestampUtc data type enforced that data stored in them was in UTC, the known time zone variant relies on annotating the entity so that the framework will know the time zone the timestamp data was stored in.

There are two variants of the known server time zone,

Server: All timestamp values are stored in the server’s time zone
Site: Timestamp values are stored in the time zone of the site related to the data row (To be added with 24R2)

This document focuses on the Server Known time zone variant.

Upgrading Data¶

Please refer to the upgrade considerations below, before deciding whether to update data or go for another approach.

If you think the data update is needed and manageable in a decent amount of time, then it can be done via a Post Installation Script or the Upgrade Script(UPG). But make sure that your other upgrade code and dependencies do align with this.

It is very important that your code considers rerunning the script to prevent duplicating data updates. This can be done using things like Patch Registration or Component Subsections.

Determining the new Timestamp value¶

There are mainly two ways you can determine the new time stamp value to the Server Time zone.

Converting Based on Time zone¶

This can be done by using Time_SYS.Convert_* methods. For example, TIME_SYS.Convert_To_System_Timezone(input_date_, from_timezone_). This can be used to convert a given date in the from_timzone directly to the server’s time zone.

Converting Based on Offset¶

The other approach is to convert based on offset. To do this you would first need to determine the offset from your source time to the server’s time zone. This can be calculated by using the Time_SYS.Get_Offset method. Please note that Time_SYS.Get_Utc_Offset(noargs) and Time_SYS.Get_Utc_Offset(time_zone) calculates offset based on the current time.

To get an accurate offset for a particular date, then use Time_SYS.Get_Utc_Offset(time_zone_, date_);

Upgrade Considerations¶

Upgrade Time¶

The first thing to think about is the data volumes at hand. The upgrade time is very critical, so if there is a large amount of data, then the typical upgrade scenario of doing a bulk update of data using UPDATE statement should not be used. This will lead to issues in customer upgrade which are hard to handle for the project teams doing the upgrade.

Rather than going for an upgrade, look into other mechanisms like treating old data (pre upgrade) differently from the data that will be created after the upgrade. This means that there will be additional logic that needs to be written to cater to the scenario, but also it means that the cost of upgrading the data (time) is avoided.

Accuracy vs Speed¶

To calculate the exact dates, you can use the Time_SYS.Convert_* functions. However, these are costly and take time to compute especially considering large amounts of data.

Another option would be, first calculating the offset and then converting that offset to date difference by using offset_in_days_ := Time_SYS.Get_Offset_Date_Difference(offset_) . Afterwards, you can do a date := date + offset_in_days_ to update the dates using a simple arithmetic operation. This approach is faster but can be inaccurate if things like Daylight Savings Time need to be considered (since the offset will change during those periods).

You can decide what way you want based on your business need.