Appboard/2.4/builder/data sources/database/date time settings - Revision history

imported>David.moore: /* Oracle */

2013-12-23T13:36:43Z

Oracle

← Older revision		Revision as of 13:36, 23 December 2013
Line 48:		Line 48:
	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.		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.

	{\|class="wikitable" border="1" style="~~text-align:center;~~ background-color:#eeeeee;"		{\|class="wikitable" border="1" style="background-color:#eeeeee;"
	!Type		!Type
	!Description		!Description

imported>David.moore: /* Which Data Type is Right for the Job? */

2013-12-23T13:36:27Z

Which Data Type is Right for the Job?

← Older revision		Revision as of 13:36, 23 December 2013
Line 12:		Line 12:
	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.		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.

	{\|class="wikitable" border="1" style="~~text-align:center;~~ background-color:#eeeeee;"		{\|class="wikitable" border="1" style="background-color:#eeeeee;"
	!Type		!Type
	!Description		!Description

imported>David.moore at 18:41, 20 December 2013

2013-12-20T18:41:17Z

← Older revision		Revision as of 18:41, 20 December 2013
Line 4:		Line 4:
	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.		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.		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 ==		== MySQL ==

imported>David.moore at 18:39, 20 December 2013

2013-12-20T18:39:37Z

← Older revision		Revision as of 18:39, 20 December 2013
Line 64:		Line 64:
	\|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.'''		\|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.

imported>David.moore at 18:34, 20 December 2013

2013-12-20T18:34:22Z

← Older revision		Revision as of 18:34, 20 December 2013
Line 43:		Line 43:

	== Oracle ==		== 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.

			{\|class="wikitable" border="1" style="text-align:center; background-color:#eeeeee;"
			!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.'''
			\|}

imported>David.moore: /* Modifying the Data Source Configuration */

2013-12-20T18:12:03Z

Modifying the Data Source Configuration

← Older revision		Revision as of 18:12, 20 December 2013
Line 34:		Line 34:
	=== Modifying the Data Source Configuration ===		=== 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.		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 ==		== Oracle ==

imported>David.moore at 18:03, 20 December 2013

2013-12-20T18:03:05Z

← Older revision		Revision as of 18:03, 20 December 2013
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 ==

imported>David.moore at 17:50, 20 December 2013

2013-12-20T17:50:18Z

@@ Line 2: / Line 2: @@
 [[Category:AppBoard 2.4]]
-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.
+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.
@@ Line 8: / Line 8: @@
 == MySQL ==
 == Oracle ==

imported>David.moore at 16:37, 20 December 2013

2013-12-20T16:37:49Z

← Older revision		Revision as of 16:37, 20 December 2013
Line 4:		Line 4:
	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.		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.

	Note: The information below applies to MySQL and Oracle and can be used general guidelines, but other database types may have a specific set of settings not address here.		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 ==		== MySQL ==

imported>David.moore at 16:37, 20 December 2013

2013-12-20T16:37:24Z

← Older revision		Revision as of 16:37, 20 December 2013
Line 2:		Line 2:
	[[Category:AppBoard 2.4]]		[[Category:AppBoard 2.4]]

	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 some extra steps to be taken that can help ensure that clients see the correct values in expected formats.		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.

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