Difference between revisions of "SQL Data Types"
From All n One's bxp software Wixi
(Tutorial on using the MySQL cast function) |
Philip Lacey (talk | contribs) |
||
| Line 2: | Line 2: | ||
| − | bxp uses the MySQL engine to store data. The data can be stored as different data types e.g. dates, numbers, text. This article will give a brief description of some of the more commonly used data types in bxp and will explain how data types can be converted for | + | bxp uses the MySQL engine to store data. The data can be stored as different data types e.g. dates, numbers, text. This article will give a brief description of some of the more commonly used data types in bxp and will explain how data types can be converted for reporting purposes. The keystats module allows users to create highly customised reports. Sometimes there is a requirement to sort, or compare data which has been entered into the form as if it's a number or date but it's stored and treated as text. |
| − | This wixi will explain how to mitigate against this | + | This wixi will explain how to mitigate against this occurring. |
| Line 29: | Line 29: | ||
|} | |} | ||
| − | In bxp, any fields that are added to a form | + | In bxp, any fields that are added to a form are added as text fields. They are the most flexible and can store multiple formats. A user may, for example, add a new field to store whole numbers. This will be stored as a text field even if all the data matches the format of an int field. A user will therefore come across some very unusual behaviour if they attempt to order a result set by this column. |
| Line 36: | Line 36: | ||
| − | If a user | + | If a user builds a database with an int column and decides to run a SQL statement to order the result by this column, they would expect the numbers to be ordered from smallest to largest: |
* 20 | * 20 | ||
| Line 43: | Line 43: | ||
* 5000 | * 5000 | ||
| − | However if a user is creating a form in bxp and adds a field to a form to store the number | + | However, if a user is creating a form in bxp and adds a field to a form to store the number, this will be stored as text. If a user orders this field it will be ordered alphabetically. So any number starting with a one will appear before a number starting with a two because it is handled alphabetically. The above list will be ordered like so: |
* 100 | * 100 | ||
| Line 50: | Line 50: | ||
* 888 | * 888 | ||
| − | This | + | This could cause issues if a user creates a keystats report and wants to order by this column. However, there is a function that can be used to treat the data as a different data type. |
| Line 57: | Line 57: | ||
| − | In MySQL there is a function called Cast which is used to convert one column of data from one | + | In MySQL there is a function called Cast which is used to convert one column of data from one data type to another. A user may have a form in bxp with an id of 938. The field that contains the numbers is strCDA_938_field_0_6. The following SQL query will order the numbers alphabetically. |
<syntaxhighlight lang="javascript"> | <syntaxhighlight lang="javascript"> | ||
| Line 78: | Line 78: | ||
| − | Another situation where the cast function can be useful is when a user needs to convert text to dates. A user may add a field to a form to hold dates and will then need | + | Another situation where the cast function can be useful is when a user needs to convert text to dates. A user may add a field to a form to hold dates, and will then need a keystats report to display data depending on the date contained in this field. The data will be stored as text and will conform to the datetime layout. |
[[File:DataTypes2.png]] | [[File:DataTypes2.png]] | ||
| − | If a user tries to create a keystat that compares the dates then the result set will be empty. | + | If a user tries to create a keystat that compares the dates, then the result set will be empty. |
<syntaxhighlight lang="javascript"> | <syntaxhighlight lang="javascript"> | ||
Latest revision as of 09:03, 29 August 2016
1 Overview
bxp uses the MySQL engine to store data. The data can be stored as different data types e.g. dates, numbers, text. This article will give a brief description of some of the more commonly used data types in bxp and will explain how data types can be converted for reporting purposes. The keystats module allows users to create highly customised reports. Sometimes there is a requirement to sort, or compare data which has been entered into the form as if it's a number or date but it's stored and treated as text.
This wixi will explain how to mitigate against this occurring.
2 Data Types
There are many different data types in excel but some of the main ones are listed below.
| Data Type | Explanation |
|---|---|
| text | A string containing numbers, letters and special characters. |
| int(m) | Any whole number is an integer and can be stored. The m represents how many charaters long the number can be |
| date | This can hold a date in the following format: YYYY-MM-DD |
| datetime | This holds a date and time in the following format: YYYY-MM-DD HH:MM:SS |
| decimal(m,d) | Any fractional number. The m is the total number of digits and the d is the number of digits after the decimal point. |
In bxp, any fields that are added to a form are added as text fields. They are the most flexible and can store multiple formats. A user may, for example, add a new field to store whole numbers. This will be stored as a text field even if all the data matches the format of an int field. A user will therefore come across some very unusual behaviour if they attempt to order a result set by this column.
3 Ordering Numbers and Text
If a user builds a database with an int column and decides to run a SQL statement to order the result by this column, they would expect the numbers to be ordered from smallest to largest:
- 20
- 100
- 888
- 5000
However, if a user is creating a form in bxp and adds a field to a form to store the number, this will be stored as text. If a user orders this field it will be ordered alphabetically. So any number starting with a one will appear before a number starting with a two because it is handled alphabetically. The above list will be ordered like so:
- 100
- 20
- 5000
- 888
This could cause issues if a user creates a keystats report and wants to order by this column. However, there is a function that can be used to treat the data as a different data type.
4 Cast Function
In MySQL there is a function called Cast which is used to convert one column of data from one data type to another. A user may have a form in bxp with an id of 938. The field that contains the numbers is strCDA_938_field_0_6. The following SQL query will order the numbers alphabetically.
Select strCDA_938_field_0_6 From CDA_938 Order By strCDA_938_field_0_6 asc
The cast function can be used to treat one column as a different data type. If a text field is cast as an integer then any functions that are applied to the field will treat the data as integers provided the data is all numeric.
The format for the cast function is "CAST(field_id AS DATATYPE)". There are eight valid data types, these are: Binary, Char, Date, Datetime, Time, Decimal, Signed and Unsigned. If you want to convert a data type to an integer then it must be cast as either a signed or Unsigned. An unsigned int is a non-negative number. A signed int can be positive or negative. The above SQL query can be amended to sort the data as numbers like so:
Select strCDA_938_field_0_6 from cda_938 Order By Cast(strCDA_938_field_0_6 AS UNSIGNED) desc
5 Date Example
Another situation where the cast function can be useful is when a user needs to convert text to dates. A user may add a field to a form to hold dates, and will then need a keystats report to display data depending on the date contained in this field. The data will be stored as text and will conform to the datetime layout.
If a user tries to create a keystat that compares the dates, then the result set will be empty.
Select strCDA_938_field_0_5 from CDA_938 where strCDA_938_field_0_5 < '2016-08-26 00:00:00' And strCDA_938_field_0_5 > '2016-08-21 00:00:00'This can be fixed by using cast. If the SQL statement is changed then it will pull back all the correct data.
Select strCDA_938_field_0_5 from CDA_938 where CAST(strCDA_938_field_0_5 AS datetime) < '2016-08-26 00:00:00' And Cast(strCDA_938_field_0_5 As datetime) > '2016-08-21 00:00:00'
There are several applications of this function. The above two are the more commonly used applications.
