Appboard/2.4/builder/data sources/database/date time settings

Depending on the timezone settings for the database, the AppBoard server, and clients connecting to AppBoard via a web browser, users may get different date and time data depending on their location. This is a result of how the data is handled as it is delivered from the database. Regardless of the timezone of the database, the AppBoard server will receive the information and convert it to the equivalent UTC date/time value. When clients connect to the AppBoard server via a web browser, the date/time value is then displayed in the timezone of the connecting client. If the database, AppBoard server, and the client are all in the same timezone, the client will typically see the data exactly as it is stored in the database with only the default settings applied to the data source. However, it is unrealistic to expect that all databases, servers, and clients will be in the same timezone, so there are some extra steps to be taken that can help ensure that clients see the correct values in expected formats. Some of these settings apply to the data source in AppBoard while others apply to the database itself.

Note: The information below applies to MySQL and Oracle and can be used as general guidelines, but other database types may have a specific set of settings not addressed here.

MySQL

Which Data Type is Right for the Job?

MySQL offers a variety of Date and Time types. Below is a listing of each type with a brief description of how the data is handled from the database to the AppBoard server. In all cases, the client displays a value that is equivalent to the data stored by the AppBoard server, but the value uses the timezone of the client.

Type	Description
Date	The database sends YYYY-MM-DD 00:00:00 GMT-0000 to the AppBoard server.
Datetime	The database sends YYYY-MM-DD HH:MM:SS GMT-XXXX (where XXXX is the timezone of the database) to the AppBoard server.
Timestamp	The database sends YYYY-MM-DD HH:MM:SS GMT-XXXX (where XXXX is the timezone of the database when the entry was made) to the AppBoard server. The database actually stores this data as the equivalent UTC value, so even if the timezone of the database changes, this value remains the same. This is the recommended data type for displaying date and time information with the correct timezone information preserved.
Time	The database sends HH:MM:SS to the AppBoard server, which takes the value with respect to 1970-01-01. Not highly recommended.
Year	The database sends YYYY-01-01 00:00:00 GMT-0000 to the AppBoard server. Useful if only the year is important for the data.

Modifying the Data Source Configuration

When using MySQL with either Datetime or Timestamp values, it is highly recommended to append to the URL the string ?useLegacyDatetimeCode=false (e.g. jdbc:mysql://192.168.56.101:3306/test_db?useLegacyDatetimeCode=false). Setting this flag seems to generate more consistent results in displaying date and time information with the correct values and format.

Another flag that has been reported to assist in the handling of the data is the serverTimezone flag. This modifies the timezone used by the database and may prove helpful in getting the desired results. A sample URL using this flag is:

jdbc:mysql://192.168.149.148:3306/claro?serverTimezone=Australia2%2FSydney&useLegacyDatetimeCode=false

Notice that the "/" is URL encoded (as %2F) and that the two flags are separated by "&".

Oracle

Which Data Type is Right for the Job?

Oracle also has a variety of Date and Time types. Below is a listing of each type with a brief description of how the data is handled from the database to the AppBoard server. In all cases, the client displays a value that is equivalent to the data stored by the AppBoard server, but the value uses the timezone of the client.

Type	Description
Date	The database sends YYYY-MM-DD 00:00:00 GMT-0000 to the AppBoard server.
Timestamp	The database sends YYYY-MM-DD HH:MM:SS GMT-0000 to the AppBoard server.
Timestamp with Time Zone	The database sends YYYY-MM-DD HH:MM:SS GMT-XXXX (where XXXX is included as part of the record in the database) to the AppBoard server. When inserting this record into the database, the user specifies the offset (XXXX) and this information gets included when the record is requested. This is one recommended data type for displaying date and time information with the correct timezone information preserved.
Timestamp with Local Time Zone	The database sends YYYY-MM-DD HH:MM:SS GMT-XXXX (where XXXX is the timezone of the AppBoard server) to the AppBoard server. When the user inserts the record into the database, the timestamp gets normalized to use the timezone of the database and then the value is stored. When the value is requested, the timestamp gets equated to the time that corresponds with the timezone of the connecting session (i.e. the AppBoard server). This is the other recommended data type for displaying date and time information with the correct timezone information preserved.

Modifying the Data Source Configuration

Unlike MySQL, currently there are no known adjustments to make in the data source configuration for an Oracle database. However, using the data types recommended above should yield positive results.