Recommendations

Time zones combine several realities: the expression may describe a constant time-lag with the UTC date, or the times of a region which may change times twice a year (daylight saving time).

For example, in postgreSQL, the SET TIME ZONE ‘Europe/Paris’; command will take summer and winter times into account: the date will be expressed in UTC+1 or UTC+2 depending on the time of year.

However, if you use the SET TIME ZONE 0200; command, the time-lag will always be UTC+2.

Configuration

The storage mode for dates and times is selected during database creation (refer to Creating a new instance). In case of a migration, the hours linked to dates are converted into local dates and hours (refer to Migration).

From a technical point of view, there are two ways of storing Date+time type information in the database:

  1. TIMESTAMP WITH TIMEZONE format: the database engine stores dates in UTC. Each session opened will have a time zone, and the dates will be converted according to it.
  2. Local format + local time zone: all dates are stored in the local format (no time-lag management) and a single time zone is assigned to them. The time zone is stored in the WdbcTimeZone option of the Adobe Campaign instance and can be changed via the Administration > Platform > Options menu of the tree.
IMPORTANT
Please be aware that this modification can result into data consistency and synchronization issues.

Creating a new instance

In order for several international users to work on the same instance, you need to configure time zones when creating the instance to manage time-lags between countries. During instance creation, select the date and time management mode in the Time zone section of the database configuration stage.

Check the UTC database (date fields with time zone) option to store all the data with dates and times in UTC format (SQL fields and XML fields).

IMPORTANT
If you are using Oracle, the timezone files (.dat) of the Oracle client layers must be compatible with the timezones files installed on the server.

If the database is not UTC, you can select one of the time zones offered in the drop-down list. You can also use the time zone of the server or select the UTC (Coordinated Universal Time) option.

When the UTC Database (date fields with time zone) option is selected, the SQL fields are stored in TIMESTAMP WITH TIMEZONE format.

Otherwise, they are stored in the local format and you will need to select the time zone to apply to the database.

Migration

When migrating to an earlier version (without time zone management), you will need to define the date storage mode in the database.

To guarantee compatibility with external tools accessing the Adobe Campaign database, the Date+time type SQL fields remain stored in local format by default.

XML fields containing dates are now stored in UTC. During loading, fields not in UTC format are converted automatically using the servers’ time zone. This means that all XML fields will progressively be converted into UTC format.

To use an existing instance, add the WdbcTimeZone option and enter the instance’s time zone.

IMPORTANT
Please make sure the correct value is configured for the WdbcTimeZone option: changes carried out later may lead to inconsistencies.

Example of possible values: