Appboard/2.4/builder/data sources/database/date time settings: Difference between revisions
imported>David.moore No edit summary |
imported>David.moore No edit summary |
||
| Line 31: | Line 31: | ||
|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. | |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:1521/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. | |||
== Oracle == | == Oracle == | ||
Revision as of 18:03, 20 December 2013
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 address 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:1521/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.
