SQL Functions

Built-in functions for date/time manipulation, type conversion, string operations, and mathematical calculations.

Date and Time Functions

Functions for parsing, formatting, and extracting date/time components.

TO_DATE - Parse Date

-- Parse string to date
UPDATE date_parsed: TO_DATE(date_string, 'YYYY-MM-DD')
FROM prices;

-- Common date formats
UPDATE
    date1: TO_DATE('2025-01-15', 'YYYY-MM-DD'),
    date2: TO_DATE('01/15/2025', 'MM/DD/YYYY'),
    date3: TO_DATE('15-Jan-2025', 'DD-Mon-YYYY')
FROM prices;

-- Parse from different column
UPDATE trade_date: TO_DATE(date_column, 'YYYYMMDD')
FROM prices;

TO_TIME - Parse Time

-- Parse string to time
UPDATE time_parsed: TO_TIME(time_string, 'HH:MI:SS')
FROM trades;

-- Common time formats
UPDATE
    time1: TO_TIME('14:30:00', 'HH24:MI:SS'),
    time2: TO_TIME('2:30 PM', 'HH:MI AM'),
    time3: TO_TIME('143000', 'HH24MISS')
FROM trades;

TO_DATETIME - Parse DateTime

-- Parse string to datetime
UPDATE datetime_parsed: TO_DATETIME(datetime_string, 'YYYY-MM-DD HH24:MI:SS')
FROM trades;

-- Common datetime formats
UPDATE
    dt1: TO_DATETIME('2025-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'),
    dt2: TO_DATETIME('01/15/2025 2:30 PM', 'MM/DD/YYYY HH:MI AM')
FROM trades;

Date Component Extraction

-- Extract year, month, day
UPDATE
    year: TO_YEAR(DATE),
    month: TO_MONTH(DATE),
    day: TO_DAY(DATE)
FROM prices;

-- Group by year and month
SELECT
    TO_YEAR(DATE) as year,
    TO_MONTH(DATE) as month,
    COUNT(*) as num_records,
    AVG(PRICE) as avg_price
FROM prices
GROUP BY TO_YEAR(DATE), TO_MONTH(DATE)
ORDER BY year, month;

Date Arithmetic

-- Add/subtract days
UPDATE
    next_week: DATE + 7,
    last_week: DATE - 7,
    next_month: DATE + 30
FROM prices;

-- Date differences
SELECT
    TICKER,
    MIN(DATE) as start_date,
    MAX(DATE) as end_date,
    MAX(DATE) - MIN(DATE) as days_span
FROM prices
GROUP BY TICKER;

Type Conversion Functions

TO_INTEGER - Convert to Integer

-- Convert to integer
UPDATE
    price_int: TO_INTEGER(PRICE),
    year_int: TO_INTEGER(TO_YEAR(DATE))
FROM prices;

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

TO_DOUBLE - Convert to Double

-- Convert string to double
UPDATE price_numeric: TO_DOUBLE(price_string)
FROM prices;

-- Ensure numeric type
UPDATE price_dbl: TO_DOUBLE(PRICE)
FROM prices;

TO_STRING - Convert to String

-- Convert to string
UPDATE
    date_str: TO_STRING(DATE),
    price_str: TO_STRING(PRICE),
    year_str: TO_STRING(TO_YEAR(DATE))
FROM prices;

-- Format numbers as strings
UPDATE price_formatted: TO_STRING(ROUND(PRICE, 2))
FROM prices;

String Functions

String Concatenation

-- Concatenate strings with +
UPDATE
    ticker_date: TICKER + '_' + TO_STRING(DATE),
    description: TICKER + ': $' + TO_STRING(PRICE)
FROM prices;

-- Build composite keys
UPDATE key: TICKER + '|' + TO_STRING(DATE)
FROM prices;

SUBSTR - Substring Extraction

-- Extract substring (1-indexed)
UPDATE
    ticker_prefix: SUBSTR(TICKER, 1, 2),
    year_str: SUBSTR(TO_STRING(DATE), 1, 4)
FROM prices;

-- Extract from position to end
UPDATE ticker_suffix: SUBSTR(TICKER, 2)
FROM prices;

UPPER and LOWER - Case Conversion

-- Convert case
UPDATE
    ticker_upper: UPPER(TICKER),
    ticker_lower: LOWER(TICKER)
FROM prices;

-- Case-insensitive comparison
SELECT * FROM prices
WHERE UPPER(TICKER) = 'AAPL';

TRIM - Remove Whitespace

-- Remove leading/trailing whitespace
UPDATE ticker_clean: TRIM(TICKER)
FROM prices;

-- Clean all string columns
UPDATE
    ticker_clean: TRIM(TICKER),
    name_clean: TRIM(company_name)
FROM companies;

Mathematical Functions

Basic Math Functions

-- Absolute value
UPDATE abs_return: ABS(daily_return)
FROM prices;

-- Round to decimal places
UPDATE
    price_rounded: ROUND(PRICE, 2),
    price_int: ROUND(PRICE, 0)
FROM prices;

-- Floor and ceiling
UPDATE
    price_floor: FLOOR(PRICE),
    price_ceil: CEIL(PRICE)
FROM prices;

Power and Square Root

-- Power function
UPDATE
    price_squared: POWER(PRICE, 2),
    price_cubed: POWER(PRICE, 3)
FROM prices;

-- Square root
UPDATE price_sqrt: SQRT(PRICE)
FROM prices;

-- Standard deviation annualization
UPDATE annualized_vol: STD(daily_return) * SQRT(252)
FROM prices
GROUP BY TICKER;

Logarithm and Exponential

-- Natural logarithm
UPDATE log_price: LOG(PRICE)
FROM prices;

-- Log returns (more stable than simple returns)
UPDATE log_return: LOG(PRICE / LAG(PRICE, 1))
FROM prices
GROUP BY TICKER;

-- Exponential
UPDATE exp_value: EXP(log_return)
FROM prices;

Conditional Functions

CASE - Conditional Logic

-- Simple CASE
UPDATE price_category: CASE
    WHEN PRICE < 50 THEN 'Low'
    WHEN PRICE < 100 THEN 'Medium'
    ELSE 'High'
END
FROM prices;

-- CASE with multiple conditions
UPDATE signal: CASE
    WHEN PRICE > EMA(PRICE, 0.1) AND daily_return > 0 THEN 'Buy'
    WHEN PRICE < EMA(PRICE, 0.1) AND daily_return < 0 THEN 'Sell'
    ELSE 'Hold'
END
FROM prices
GROUP BY TICKER;

-- Searched CASE
UPDATE risk_level: CASE
    WHEN volatility > 0.03 THEN 'High Risk'
    WHEN volatility > 0.015 THEN 'Medium Risk'
    WHEN volatility > 0 THEN 'Low Risk'
    ELSE 'Unknown'
END
FROM price_stats;

COALESCE - NULL Handling

-- Replace NULL with default value
UPDATE
    price_clean: COALESCE(PRICE, 0),
    volume_clean: COALESCE(VOLUME, 0)
FROM prices;

-- Multiple fallback values
UPDATE company: COALESCE(company_name, short_name, TICKER)
FROM companies;

-- Use previous value if NULL
UPDATE price_filled: COALESCE(PRICE, LAG(PRICE, 1))
FROM prices
GROUP BY TICKER;

NULLIF - Create NULL

-- Convert specific value to NULL
UPDATE price_null: NULLIF(PRICE, 0)
FROM prices;

-- Prevent division by zero
UPDATE price_ratio: PRICE / NULLIF(prev_price, 0)
FROM prices;

Practical Examples

Example 1: Date Parsing and Extraction

-- Load data with date string
.load '/data/bt/20251208W/POLARIS1/data/prices.csv' as prices

-- Parse and extract date components
UPDATE
    year: TO_YEAR(DATE),
    month: TO_MONTH(DATE),
    day: TO_DAY(DATE),
    quarter: CASE
        WHEN TO_MONTH(DATE) <= 3 THEN 1
        WHEN TO_MONTH(DATE) <= 6 THEN 2
        WHEN TO_MONTH(DATE) <= 9 THEN 3
        ELSE 4
    END,
    year_month: TO_STRING(TO_YEAR(DATE)) + '-' +
                CASE WHEN TO_MONTH(DATE) < 10
                     THEN '0' + TO_STRING(TO_MONTH(DATE))
                     ELSE TO_STRING(TO_MONTH(DATE))
                END
FROM prices;

-- Monthly aggregation
SELECT
    year_month,
    COUNT(*) as num_records,
    AVG(PRICE) as avg_price
FROM prices
GROUP BY year_month
ORDER BY year_month;

Example 2: Signal Generation with Functions

-- Load and prepare data
.load '/data/prices.csv' as prices

-- Calculate features
UPDATE
    prev_price: LAG(PRICE, 1),
    daily_return: PRICE / LAG(PRICE, 1) - 1,
    price_ema20: EMA(PRICE, 0.095),
    price_ema50: EMA(PRICE, 0.039),
    volume_ema: EMA(VOLUME, 0.1)
FROM prices
GROUP BY TICKER;

-- Generate signals
UPDATE
    trend_signal: CASE
        WHEN price_ema20 > price_ema50 THEN 1
        WHEN price_ema20 < price_ema50 THEN -1
        ELSE 0
    END,
    momentum_signal: CASE
        WHEN PRICE / LAG(PRICE, 20) - 1 > 0.1 THEN 1
        WHEN PRICE / LAG(PRICE, 20) - 1 < -0.1 THEN -1
        ELSE 0
    END,
    volume_signal: CASE
        WHEN VOLUME > volume_ema * 1.5 THEN 1
        ELSE 0
    END
FROM prices
GROUP BY TICKER;

-- Combined signal
UPDATE composite_signal:
    trend_signal + momentum_signal + volume_signal
FROM prices;

Example 3: Data Cleaning Pipeline

-- Load raw data
.load '/data/raw_prices.csv' as prices

-- Clean string columns
UPDATE
    TICKER: TRIM(UPPER(TICKER)),
    company_name: TRIM(company_name)
FROM prices;

-- Handle NULL values
UPDATE
    PRICE: COALESCE(PRICE, LAG(PRICE, 1)),
    VOLUME: COALESCE(VOLUME, 0)
FROM prices
GROUP BY TICKER;

-- Remove invalid records
DELETE FROM prices
WHERE PRICE IS NULL
   OR PRICE <= 0
   OR TRIM(TICKER) = '';

-- Add quality flags
UPDATE
    price_change_pct: ABS(PRICE / LAG(PRICE, 1) - 1),
    is_valid: CASE
        WHEN ABS(PRICE / LAG(PRICE, 1) - 1) > 0.5 THEN 0
        WHEN PRICE <= 0 THEN 0
        ELSE 1
    END
FROM prices
GROUP BY TICKER;

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

Example 4: Performance Metrics Calculation

-- Load price data
.load '/data/prices.csv' as prices

-- Calculate returns and features
UPDATE
    daily_return: PRICE / LAG(PRICE, 1) - 1,
    log_return: LOG(PRICE / LAG(PRICE, 1))
FROM prices
GROUP BY TICKER;

-- Performance metrics by ticker
SELECT
    TICKER,
    COUNT(*) as num_days,

    -- Return metrics
    AVG(daily_return) * 252 as annualized_return,
    STD(daily_return) * SQRT(252) as annualized_volatility,
    (AVG(daily_return) * 252) / (STD(daily_return) * SQRT(252)) as sharpe_ratio,

    -- Price metrics
    FIRST(PRICE) as start_price,
    LAST(PRICE) as end_price,
    MIN(PRICE) as min_price,
    MAX(PRICE) as max_price,
    LAST(PRICE) / FIRST(PRICE) - 1 as total_return,

    -- Downside metrics
    SQRT(AVG(POWER(CASE WHEN daily_return < 0
                         THEN daily_return
                         ELSE 0 END, 2))) * SQRT(252) as downside_deviation,

    -- Win rate
    AVG(CASE WHEN daily_return > 0 THEN 1.0 ELSE 0.0 END) as win_rate
FROM prices
GROUP BY TICKER
HAVING COUNT(*) >= 100
ORDER BY sharpe_ratio DESC;