Querying Dynamically Named Day-of-Month Columns in SQLite

Schema Design Limitations for Day-Specific Column Filtering

The core challenge revolves around querying a denormalized table structure where day-of-month values are stored in separate columns (Area01 to Area31) instead of being normalized. This design forces users to reference columns dynamically based on the current date, which SQLite’s static schema architecture does not natively support. The table’s rigid structure creates three fundamental problems:

1. Column Name Rigidity: SQL requires explicit column names in WHERE clauses at query compile time. There is no syntax to programmatically construct column names using expressions (e.g., Area||strftime('%d')).
2. Data Model Fragility: Storing day-specific values across 31 columns creates maintenance overhead and complicates queries that need to compare or aggregate daily values.
3. Type Safety Ambiguity: All columns are defined as TEXT, but the stored values (e.g., ‘8’, ’16’) suggest numerical data, inviting comparison errors when using string-based operators like <='6'.

These limitations manifest when attempting to filter rows based on the current day’s corresponding Area## column. For example, on the 13th day of the month, the query needs to compare Area13 against a value, but hardcoding Area13 in the WHERE clause defeats automation. The schema’s denormalized nature forces workarounds that bypass SQLite’s type system and optimization capabilities.

Dynamic Column Selection Constraints in Static Schemas

The inability to reference columns dynamically stems from SQLite’s compile-time parsing of table metadata. When a query is prepared, the SQLite engine resolves column names against the schema catalog before executing any expressions. This means:

• Metadata Immutability: Column names exist as fixed identifiers in the schema. They cannot be parameterized or derived from runtime values like dates.
• Expression Evaluation Order: SQL expressions (e.g., strftime('%d')) are evaluated after the query plan is generated. Thus, they cannot influence which columns are accessed.
• Projection Limitations: While GROUP BY and ORDER BY allow referencing columns by their ordinal position in the SELECT list (e.g., GROUP BY 3), this does not extend to filtering or other clauses.

These constraints make it impossible to directly write a WHERE clause that dynamically targets Area13 on the 13th, Area14 on the 14th, etc., using standard SQL syntax. The workarounds fall into two categories: (1) leveraging SQLite’s expression engine to map dates to columns, or (2) generating SQL statements programmatically outside the database.

Pragmatic Workarounds for Schema-Driven Filtering

CASE-WHEN Conditional Column Mapping

The most straightforward in-database solution uses a CASE expression to map the current day to the corresponding Area## column:

SELECT TimeFrom, TimeTo, Stage 
FROM Sch 
WHERE 
  CASE CAST(strftime('%d', 'now') AS INTEGER)
    WHEN 1 THEN Area01
    WHEN 2 THEN Area02
    ...
    WHEN 31 THEN Area31
  END = '8'
  AND Stage <= '6';

Advantages:

• No schema changes required.
• Works within a single query.

Drawbacks:

• Maintenance Burden: The CASE expression must enumerate all 31 days, making the query unwieldy.
• Performance Impact: SQLite cannot use indexes on the Area## columns because the CASE expression obscures the actual columns being compared.
• Type Coercion Risks: Comparing the CASE result (TEXT) to the string ‘8’ may fail if numeric comparisons are needed (e.g., > 7).

Normalization via Schema Refactoring

Restructuring the table to a normalized form eliminates the need for dynamic column references:

CREATE TABLE Sch_Normalized (
  TimeFrom TEXT,
  TimeTo TEXT,
  Stage TEXT,
  Day INTEGER,
  Area TEXT
);

INSERT INTO Sch_Normalized
SELECT TimeFrom, TimeTo, Stage, 1 AS Day, Area01 AS Area FROM Sch UNION ALL
SELECT TimeFrom, TimeTo, Stage, 2 AS Day, Area02 AS Area FROM Sch UNION ALL
...
SELECT TimeFrom, TimeTo, Stage, 31 AS Day, Area31 AS Area FROM Sch;

Querying becomes trivial:

SELECT TimeFrom, TimeTo, Stage 
FROM Sch_Normalized 
WHERE Day = CAST(strftime('%d', 'now') AS INTEGER)
  AND Area = '8'
  AND Stage <= '6';

Advantages:

• Simplifies all future queries involving daily data.
• Enables indexing on Day and Area for faster lookups.
• Facilitates aggregation (e.g., monthly averages).

Drawbacks:

• Migration Effort: Requires transforming existing data, which may be time-consuming for large tables.
• Storage Overhead: Normalization increases row count by a factor of 31, though compression can mitigate this.

External SQL Generation via CLI Scripting

When schema changes are impossible, generate the SQL dynamically using the SQLite command-line interface (CLI):

# Generate the query
DAY=$(date +%d)
QUERY="SELECT TimeFrom, TimeTo, Stage FROM Sch WHERE Area${DAY} = '8' AND Stage <= '6';"

# Execute the query
sqlite3 your_db.db "$QUERY"

Or entirely within the CLI:

.mode list
.once query.sql
SELECT 'SELECT TimeFrom, TimeTo, Stage FROM Sch WHERE Area' || strftime('%d','now') || ' = ''8'' AND Stage <= ''6'';'
.read query.sql

Advantages:

• Avoids massive CASE statements.
• Leverages shell scripting or CLI features to handle dynamic aspects.

Drawbacks:

• Security Risks: Externally generated SQL is prone to injection if inputs are not sanitized.
• Toolchain Dependency: Requires orchestration outside SQLite (e.g., bash, Python).

Hybrid Approach: Prepared Statements with sqlite3_prepare_v2

For programmatic access, use SQLite’s C API (or a wrapper library) to prepare statements with dynamically generated column names:

int day = ...; // Get current day
char sql[100];
sprintf(sql, "SELECT TimeFrom, TimeTo, Stage FROM Sch WHERE Area%02d = ? AND Stage <= ?;", day);
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, "8", -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, "6", -1, SQLITE_STATIC);
// Execute and fetch results

Advantages:

• Combines dynamic SQL with parameter binding to prevent injection.
• Efficient execution plan caching for repeated queries.

Drawbacks:

• Requires programming in C/Python/etc., not pure SQL.

Type Consistency Adjustments

Converting Area## columns to INTEGER ensures correct comparisons:

ALTER TABLE Sch ADD COLUMN Area01_INT INTEGER;
UPDATE Sch SET Area01_INT = CAST(Area01 AS INTEGER);
-- Repeat for Area02 to Area31, then drop original columns

Or during querying:

WHERE CAST(
  CASE ... END AS INTEGER
) >= 8

Advantages:

• Fixes incorrect comparisons caused by string ordering (e.g., ’10’ < ‘2’).

Drawbacks:

• Requires data type changes, which may not be feasible if the schema is controlled externally.

Materialized Views for Column Abstraction

Create a view that dynamically maps the current day’s column:

CREATE VIEW CurrentDaySch AS
SELECT 
  TimeFrom,
  TimeTo,
  Stage,
  CASE CAST(strftime('%d', 'now') AS INTEGER)
    WHEN 1 THEN Area01
    ...
  END AS CurrentArea
FROM Sch;

SELECT TimeFrom, TimeTo, Stage 
FROM CurrentDaySch 
WHERE CurrentArea = '8' AND Stage <= '6';

Advantages:

• Abstracts complexity into a reusable view.
• Centralizes the CASE logic.

Drawbacks:

• The view must be recreated or altered daily, as strftime('%d', 'now') is evaluated at view creation time, not query time.

Summary of Fixes

Recommendation:
If possible, normalize the schema. This future-proofs the data model and simplifies all subsequent queries. If the schema cannot be altered, use external SQL generation or prepared statements to dynamically construct queries. Reserve the CASE-WHEN approach for ad-hoc queries where other methods are impractical. Always validate data types to prevent comparison errors.