Spectre Data Types

The string data type is a sequence of characters.

Examples of string data are: "34 Main Street", "Dubow, Alexandra"

• Use for words, numbers and characters, codes, descriptions
• Most flexible type—will take anything
• Not the fastest type to work with

The integer data type is a 64-bit signed integer number.

Examples of integer data are: 6538, 78329, -789000

• Infer when all column values are numeric with zero decimal places
• Uses integer math
• Non-integer numbers load as unknown

The fixed100 data type is a fixed-point integer value, where the decimal point is fixed between the second and third digit from the end, providing hundredths.

Examples of fixed100 data are: 154.65, 543290.07

• Use for numbers with 1 or 2 decimal places; ideal for currencies
• Infer when all column values are numeric and have 0, 1, or 2 decimals
• 64-bit signed decimal fixed-point number with a denominator of 100
• Provides greater accuracy in computations than the double data type
• Faster than double (integer versus floating-point math)
• Might use less memory than double, improving memory throughput
• Representable in fewer than 8 bytes (1, 2, or 4, depending on range of values)
• Data with more than two decimal places are rejected due to loss of precision and load as unknown
• Specifying an incorrect data type results in an "error" value

The double data type is a double-precision floating-point value.

Examples of double data are: 12.6453, 1954.8835

• Use for numbers with more than two decimal places, for example, per unit calcs where fractional pennies are important
• Computations that can be less accurate than fixed100—imprecisions for floating-point numbers tend to emerge with large volumes of data. As a result of that imprecision, you cannot dive on a double.(for more information about precision, see the kahan_sum summary function)
• Uses 8 bytes to represent a number, which can be more than fixed100 uses
• Slower than fixed100 (floating-point versus integer math)
• 64-bit signed binary floating-point number
• Non-numeric data loads as unknown

The boolean data type has only two possible values, usually denoted as true or false.

Examples of Boolean data are: True or False, Yes or No

• Alternative formats (for example "T;F", "y;n")
• Blank values considered false or null, depending on format
• Case-insensitive

The Date data type is generally formatted into a readable form that looks like a string. When a data column or variable is identified as a date with a certain format, Spectre stores the date data internally to allow for the traditional day calculations.

Examples of date data are:  2009/03/01 and January 1, 2016

• 32-bit signed integer
• An internal format that facilitates date manipulations
• Generally computed using a function provided by the expression context, for example, date("2012/10/01")
• Date strings need to be defined as date or period in the cBase to use them in time series or date calculations
• Date data must represent a valid date
• Subtract two dates to produce an integer number of days between them
• Calculate an offset date by adding or subtracting a number of days
• A calculation involving two dates, one of which is null, returns null

The period data type is a time range between a start point and an end point. Periods are defined in terms of a calendar.

Examples of period data are:  2016-W10/iso 8601 year-week, 2016 Q4/standard year-month

• Several built-in calendars are supported: standard, iso 8601, offset <month>
• Custom calendars can be defined and built into cBases for easy referencing
• When defining a period, an input and output format can be specified
• Period data must be a valid period for the calendar specified

The datetime data type combines both a date and a time-of-day into a single value. The time portion ranges from 12:00:00 in the morning through 11:59:59 at night.

Examples of datetime data are: 2015-01-03 10:51:03, 2015/01/03 10:51:03, 01/03/2015 10:51:03 and Jan 3, 2015 10:51:03

• Accepts SQL database DateTime fields
• Facilitates elapsed time calculations from Database timestamps
• If the DB datetime field includes milliseconds, use the scan function to discard them.
• A duration attribute is available to format a time column as h:mm:ss
• A zone attribute can be used to store time data as GMT in the cBase

Spectre has two special values that it can use in expressions: null and unknown. These values behave differently from normal values in an expression, and that different behavior allows Spectre to handle certain special cases. In general:

• null indicates "no data"
• unknown indicates an "un-computable value"

NOTE: For more information, see the reference links about null values in Spectre and unknown values in Spectre.

Spectre efficiency relies on the strict adherence to type for data built into cBases.

• Be sure to catch any data issues at load time—when you are first building the cBase.
• You can read the data as a string and do some validation before casting it into a stricter data type.
• Spectre functions such as parse_integer , catch, and fixed100 can be helpful.
• You can determine if a date, period, and datetime value is empty using the notnull Spectre function.
• Decide how to handle any issues: bad data, questionable valid data, and other data typing issues.
• If you want to see the data values, use a new column, separate from your primary column, to save the incoming data, and another column to save the reason why the data is being questioned.
• Use of date-number data (the binary number) in a Spectre build is not straightforward. Try this work-around:

build {
  text-input "demo_drl.txt" {
    column "Date" type="integer"  
    . . .
  }
  replace "Date" `date_from_date_number(value("Date"))`
}