Internationalization configuration and dates

Goal

When working with date fields, you can find that the values returned for those fields do not match the values that you are expecting. Usually, these problems with dates are related to an incorrect internationalization configuration.

In this document we will analyze the different internationalization options available.

NOTE: This document applies to Denodo 6.0 and earlier versions. For Denodo 7.0 and newer versions this only applies to fields with data type date (deprecated).

Content

The dates retrieved from a data source will be read with the i18n that you have configured for the base view.

When we create a new base view, the i18n established will be determined by the server’s Default i18n value. It can be changed in Administration-> Server configuration-> Default I18n.

In order to change the i18n configuration in a specific base view, double click on the base view, and go to the Options > Search Methods tab.

Since the data from the source will be read with this locale value, the recommendation is to configure this value to have the same locale as the data source. Otherwise, the date will be retrieved with an incorrect locale.

Example:

Let’s imagine that we have a MySQL server in New York (EST) and we are connecting to this data source from a VDP server from San Francisco (PST) which has as default i18n “us_pst”.

If we create a base view, the i18n value for this new base view is going to be “us_pst”. Which leads us to:

• Date in the source: Jan 14, 2015 4:45:22 PM EST.
• Date delivered by VDP: Jan 14, 2015 4:45:22 PM PST.

This value is not correct because the i18n configuration for the base view is not the same as the configuration in the MySQL database server.

If we change the i18n to “us_est”.

• Date in the source: Jan 14, 2015 4:45:22 PM EST.
• Date delivered by  VDP: Jan 14, 2015 4:45:22 PM EST.

Now, the value for the time is correct. For this reason, in order to avoid mismatching issues, the base view has to be configured with the same i18n as the source.

If you are using the VDP Admin Tool to execute queries, the tool will automatically transform the dates to the locale configuration of the local machine where the VDP Admin Tool is running.

As a consequence, if your local machine does not use the same locale as the VDP server, this could cause possible differences between the actual date and the date displayed in the VDP Admin Tool.

Let’s suppose that we have a VDP Server running in New York (EST) and we are connected to it using a VDP Admin Tool from San Francisco (PST).

• Date delivered by the VDP server: Jan 14, 2015 4:45:22 PM EST.
• Date displayed by the VDP Admin Tool: Jan 14, 2015 1:45:22 PM PST.

You can see that the VDP Admin Tool is transforming the date in order to show the date value using the VDP Admin Tool time zone.

This has an important implication. When executing queries, the where clauses on date fields have to be written taking into account the timezone of the server, not the Admin Tool.

So, if we run the following query:

SELECT mydate FROM myview WHERE mydate= 'Jan 14, 2015 4:45:22 PM EST'

The tuples retrieved will have the value mydate = 'Jan 14, 2015 1:45:22 PM PST'.

However, if you run the query SELECT mydate FROM myview WHERE mydate= 'Jan 14, 2015 1:45:22 PM PST'  the query will not return results.

To avoid issues with the date format, it is highly recommended to use the to_date function when you specify the date pattern. For example, you could execute queries as follows:

SELECT * FROM mytime WHERE mydate = to_date('dd-MM-yyyy HH:mm:ss','14-01-2015 04:45:22')

Using this query, you will get the same result as in the first query. You still need to write the date using the VDP server’s time zone,  but you will be able to use the format that you specify as the first argument of the function instead of the one in the server’s configuration.

In order to have the VDP server locale configuration be displayed in your VDP Admin tool, you need to select the "Internationalize query results" option and select the i18n value for the server you are connecting to. The "Internationalize query results" option considers the i18n used by the query. If you do not specify the i18n in an explicit way using the CONTEXT clause, then the default i18n selected in the Admin Tool is used.

Let’s suppose again that we have a VDP Server running in New York (EST) and we are connected to it using a VDP Admin Tool from San Francisco (PST).

But now, we select “us_est” as i18n and check the option "Internationalize query results". To do that, in the VDP Admin Tool, go to Tools -> Admin Tool Preferences -> Locale.

Now, if we execute the query:

select mydate from myview where mydate='Jan 14, 2015 4:45:22 PM EST'

The retrieved results will look like: mydate = 'Jan 14, 2015 4:45:22 PM EST'

References

Virtual DataPort Administration Guide: Internationalization Configuration

Virtual DataPort Administration Guide: Configuring the Default Internationalization