Type Conversion Functions

TO_DATE - Convert to Date

Convert from a numerical value or string (format: "YYYY-MM-DD") to DATE type.

-- Convert string to date
SELECT TICKER, DATE, PRICE
FROM prices
WHERE DATE = to_date("2025-01-15");

-- Convert in UPDATE statement
UPDATE parsed_date: to_date(date_string)
FROM raw_data;

-- Use in date comparisons
SELECT * FROM prices
WHERE DATE >= to_date("2025-01-01")
  AND DATE <= to_date("2025-12-31");

Date Filtering Examples

-- Filter by specific date
SELECT TICKER, PRICE
FROM prices
WHERE DATE = to_date("2025-01-15");

-- Date range query
SELECT TICKER, AVG(PRICE) as avg_price
FROM prices
WHERE DATE BETWEEN to_date("2025-01-01")
              AND to_date("2025-01-31")
GROUP BY TICKER;

TO_STRING - Convert to String

Syntax: to_string(COLUMN, NUM_CHARS)

Convert any value to VARCHAR type. The number of characters must be specified. Data will be truncated at the maximum number of characters.

-- Convert to string with length limit
UPDATE
    ticker_short: to_string(TICKER, 5),
    price_str: to_string(PRICE, 10),
    date_str: to_string(DATE, 20)
FROM prices;

-- Create composite keys
UPDATE key: to_string(TICKER, 10) + '_' + to_string(DATE, 20)
FROM prices;

-- Format for display
SELECT
    TICKER,
    to_string(PRICE, 8) as price_formatted,
    to_string(VOLUME, 12) as volume_formatted
FROM prices;

String Truncation Example

-- Truncate to specific length
SELECT
    TICKER,
    to_string(TICKER, 3) as ticker_abbr
FROM prices;

-- Result: "AAPL" becomes "AAP"
-- Result: "MSFT" becomes "MSF"

TO_TIME - Convert to Time

Convert from a numerical value or string (format: "HH:MM:SS.000") to TIME type.

-- Convert string to time
UPDATE trade_time: to_time("10:30:00.000")
FROM trades;

-- Parse time from string column
UPDATE
    parsed_time: to_time(time_string),
    hour: TO_INTEGER(SUBSTR(time_string, 1, 2))
FROM raw_trades;

-- Filter by time
SELECT TICKER, PRICE, trade_time
FROM trades
WHERE trade_time >= to_time("09:30:00.000")
  AND trade_time <= to_time("16:00:00.000");

Time-based Analysis

-- Add timestamp columns
UPDATE
    market_open: to_time("09:30:00.000"),
    market_close: to_time("16:00:00.000"),
    trade_time: to_time(timestamp_string)
FROM trades;

-- Intraday analysis
SELECT
    TICKER,
    trade_time,
    PRICE,
    CASE
        WHEN trade_time < to_time("10:00:00.000") THEN 'MORNING'
        WHEN trade_time < to_time("14:00:00.000") THEN 'MIDDAY'
        ELSE 'AFTERNOON'
    END as session
FROM trades;

Numeric Type Conversions

TO_LONG - Convert to Long Integer

-- Convert to long integer (64-bit)
SELECT
    TICKER,
    PRICE,
    to_long(PRICE) as price_long,
    to_long(VOLUME) as volume_long
FROM prices;

-- Truncates decimal part
UPDATE
    price_int: to_long(PRICE),
    rounded_price: to_long(PRICE + 0.5)
FROM prices;

TO_INTEGER - Convert to Integer

-- Convert to integer (32-bit)
SELECT
    TICKER,
    PRICE,
    to_integer(PRICE) as price_int
FROM prices;

-- Convert rank to integer
UPDATE decile: to_integer(WRANK(PRICE, 10))
FROM prices;

-- Binning continuous values
UPDATE price_bucket: to_integer(PRICE / 10) * 10
FROM prices;

TO_UNSIGNED_INTEGER - Convert to Unsigned Integer

-- Convert to unsigned integer (positive values only)
UPDATE
    volume_uint: to_unsigned_integer(VOLUME),
    count_uint: to_unsigned_integer(COUNT(*))
FROM prices
GROUP BY TICKER;

-- For positive-only data
UPDATE
    days_since: to_unsigned_integer(DATE - to_date("2025-01-01"))
FROM prices;

TO_FLOAT - Convert to Float

-- Convert to floating point
SELECT
    TICKER,
    VOLUME,
    to_float(VOLUME) as volume_float,
    to_float(VOLUME) / 1000000 as volume_millions
FROM prices;

-- Ensure floating point arithmetic
UPDATE
    ratio: to_float(PRICE) / to_float(LAG(PRICE, 1)),
    pct_change: (to_float(PRICE) / to_float(LAG(PRICE, 1)) - 1) * 100
FROM prices
GROUP BY TICKER;

Type Conversion Matrix

Overview of available type conversions.

Practical Examples

Example 1: Data Type Conversions in ETL

-- Load raw data with string columns
.load '/data/raw_data.csv' as raw_data

-- Convert data types
UPDATE
    -- Date conversions
    trade_date: to_date(date_string),
    trade_time: to_time(time_string),

    -- Numeric conversions
    price_float: to_float(price_string),
    volume_long: to_long(volume_string),
    shares_int: to_integer(shares_string),

    -- String formatting
    ticker_clean: to_string(TRIM(ticker_raw), 10),
    price_formatted: to_string(to_float(price_string), 12)
FROM raw_data;

-- Filter and validate
DELETE FROM raw_data
WHERE trade_date IS NULL
   OR price_float <= 0;

-- Save cleaned data
.save raw_data AS '/data/clean_data.adb' parameter(format=binary)

Example 2: Creating Composite Keys

-- Create unique identifiers
UPDATE
    -- Date-based key
    date_key: to_string(TO_YEAR(DATE), 4) +
              to_string(TO_MONTH(DATE), 2) +
              to_string(TO_DAY(DATE), 2),

    -- Full composite key
    composite_key: to_string(TICKER, 10) + '|' +
                   to_string(DATE, 20) + '|' +
                   to_string(to_integer(PRICE * 100), 10),

    -- Hash-like key from integer
    hash_key: to_string(to_integer(PRICE * 1000) % 10000, 10)
FROM prices;

-- Use for joining
CREATE TABLE lookup AS (
    SELECT
        composite_key,
        TICKER,
        DATE,
        PRICE
    FROM prices
);

Example 3: Binning and Categorization

-- Discretize continuous variables
UPDATE
    -- Price buckets
    price_bucket: to_integer(PRICE / 10) * 10,

    -- Volume categories
    volume_category: CASE
        WHEN to_long(VOLUME) < 100000 THEN 'LOW'
        WHEN to_long(VOLUME) < 1000000 THEN 'MEDIUM'
        ELSE 'HIGH'
    END,

    -- Decile bins
    price_decile: to_integer(WRANK(PRICE, 10)),

    -- Date components for grouping
    year: to_integer(TO_YEAR(DATE)),
    month: to_integer(TO_MONTH(DATE)),
    quarter: to_integer((TO_MONTH(DATE) - 1) / 3) + 1
FROM prices;

-- Aggregate by bins
SELECT
    price_bucket,
    COUNT(*) as num_records,
    AVG(PRICE) as avg_price
FROM prices
GROUP BY price_bucket
ORDER BY price_bucket;

Example 4: Format Conversion Pipeline

-- Convert between different formats
UPDATE
    -- Original values
    original_date: DATE,
    original_price: PRICE,

    -- String representations
    date_iso: to_string(DATE, 20),
    date_key: to_string(TO_YEAR(DATE), 4) +
              '-' +
              to_string(TO_MONTH(DATE), 2) +
              '-' +
              to_string(TO_DAY(DATE), 2),

    -- Numeric representations
    date_int: to_integer(TO_YEAR(DATE) * 10000 +
                         TO_MONTH(DATE) * 100 +
                         TO_DAY(DATE)),
    price_cents: to_integer(PRICE * 100),

    -- Type conversions for calculations
    price_float: to_float(PRICE),
    volume_float: to_float(VOLUME),

    -- Back to string for export
    export_string: to_string(TICKER, 10) + ',' +
                   to_string(DATE, 20) + ',' +
                   to_string(PRICE, 15)
FROM prices;

-- Export formatted data
.save prices AS '/data/formatted_export.csv'