Configuring the Default Internationalization¶

The Virtual DataPort server internationalization configuration specifies aspects such as time zones.

Virtual DataPort includes typical internationalization configurations and you can also create new ad hoc configurations (see section Managing Internationalization Configurations of the VQL Guide).

A specific internationalization configuration can also be specified for each Virtual DataPort database (see section Configuring and Deleting Databases)

The Administration Tool also has its own internationalization configuration that can be changed on the tab Locale of the dialog Tools > Admin Tool Preferences (see section Locale)

Take the following into account to understand how the i18n of the Denodo server, its databases and their base views affect the queries:

1. Denodo does not have the concept of timestamp without time zone. In Denodo, timestamp values (fields of type “date” without subtype or the subtype TIMESTAMP) always include a time zone.

2. Denodo uses the i18n of the base view to assign a time zone to the timestamp values obtained from the source (a database, a file, etc.) when these values do not have a time zone.

   If a base view has a field of type date that is obtained from Oracle and in Oracle this field has the type TIMESTAMP, Denodo assigns the time zone of the i18n of the base view to the values of this field. If in Oracle, the field has the type TIMESTAMP WITH TIMEZONE, the value already has a time zone so Denodo does not assign one.

   For example, if in the database the type of the field is TIMESTAMP, the value is “2017-08-29 10:19:41” and the i18n of the base view is “us_pst”, Denodo assigns the time zone “-07:00”. So Denodo considers that the value is “2017-08-29 10:19:41 -07:00”.

   If in Oracle the type of the field is TIMESTAMP WITH TIMEZONE and the value is “2017-08-29 10:19:41 -04:00”, this will be the value regardless of the i18n of the base view. As we explain below, the representation of the timestamp may change depending on the client, but the value will represent the same instant in time. E.g. “2017-08-29 10:19:41 -04:00” may be represented like “2017-08-29 07:19:41 -07:00” (the hour is “07” instead of “10” but as the time zone is -07:00 instead of -04:00, it is the same instant in time)

3. Some tools may represent timestamps differently:

   • If you run the query from the administration tool:

     • If you enabled Internationalize Query Results on the administration tool: the Tool displays the timestamps using the date pattern associated with the i18n selected. This is the i18n selected on the menu Tools > Admin tool preferences > tab Locale).

     • If you cleared Internationalize Query Results on the administration tool: the Tool displays the timestamps using the date pattern of the locale of the host where the administration tool - not the Server - runs. As the date values returned by the Denodo Server have a time zone, the result is the same regardless of the locale of the host; the change is that the time zone is different. So if this option is cleared and my administration tool is in California, it will display “Thu Aug 31 15:26:08 PDT 2017”; if I am in the East Coast, I will get “Thu Aug 31 18:26:08 EDT 2017”. Both values represent the same instant in time represented in a different time zone.

   • If you run the query from a JDBC application (e.g. DBVisualizer), Denodo returns what the JDBC API requires (a java.sql.Date object), which is a timestamp with a time zone. It is up to the client to see how to display this. Some display these values with their time zone, other do not, which can lead to confusion.

4. By default, all the Denodo databases have the same i18n as the Server. Although you can configure each database to have a different i18n, we advise against it because it makes the configuration of the Server more complex.

5. When you create a new base view, it has the i18n of the Server (or the i18n of the Denodo database if it is different). This is, what you see selected in the dialog Administration > Server configuration > Default i18n tab. The i18n of the base view can be changed later.

6. When retrieving fields of type “date” from a delimited file source (DF), these fields must have the format expected by the selected i18n.

   You can see the date format associated to an i18n by executing the statement DESC VQL MAP I18N <name of the map>. The output of this command contains a value called datepattern that expresses the expected format of the date in the Java date format syntax (see the section Date and Time Pattern Strings of the VQL Guide for details about this syntax).

Virtual DataPort includes the most usual internationalization configurations, but also allows new configurations to be created ad hoc. See section Managing Internationalization Configurations in the VQL Guide for more information.