Date and Time Handling

Date and Time Handling

This document explains how to work with dates and times in Stoolap based on the implementation and test files.

TIMESTAMP Data Type

Stoolap uses the TIMESTAMP data type for storing date and time values. When defining a table schema, you can use this type for any columns that need to store temporal data:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date TIMESTAMP,
    created_at TIMESTAMP
);

Supported Date and Time Formats

Based on the implementation, Stoolap recognizes various date and time formats:

ISO 8601 Format

2023-05-15T14:30:45Z          -- ISO format with UTC timezone (Z)
2023-05-15T14:30:45+00:00     -- ISO format with explicit timezone
2023-05-15T14:30:45           -- ISO format without timezone

SQL-Style Format

2023-05-15 14:30:45.123       -- SQL format with milliseconds
2023-05-15 14:30:45           -- SQL format without fractional seconds

Date Only

2023-05-15                    -- Date only (time defaults to 00:00:00)

Alternative Formats

2023/05/15 14:30:45           -- Alternative format with slashes
2023/05/15                    -- Alternative date only
05/15/2023                    -- US format (month/day/year)
15/05/2023                    -- European format (day/month/year)

Time Only

14:30:45.123                  -- Time with milliseconds
14:30:45                      -- Standard time
14:30                         -- Hours and minutes only
2:30:45 PM                    -- 12-hour format

Date and Time Functions

NOW()

Returns the current date and time:

-- Insert the current timestamp
INSERT INTO events (id, event_name, created_at)
VALUES (1, 'Meeting', NOW());

-- Filter for records created today
SELECT * FROM events WHERE created_at >= NOW();

DATE_TRUNC(unit, timestamp)

Truncates a timestamp to the specified precision:

-- Truncate to day (removes time component)
SELECT DATE_TRUNC('day', event_date) FROM events;

-- Truncate to month (sets day to 1 and time to 00:00:00)
SELECT DATE_TRUNC('month', event_date) FROM events;

Supported units:

  • year - Truncates to the beginning of the year
  • month - Truncates to the beginning of the month
  • day - Truncates to the beginning of the day
  • hour - Truncates to the beginning of the hour
  • minute - Truncates to the beginning of the minute
  • second - Truncates to the beginning of the second

TIME_TRUNC(interval, timestamp)

Truncates a timestamp to a specific time interval, useful for time series data:

-- Truncate to 15-minute intervals
SELECT TIME_TRUNC('15m', event_time) FROM events;

-- Truncate to 1-hour intervals
SELECT TIME_TRUNC('1h', event_time) FROM events;

The interval parameter accepts duration strings like:

  • 15m - 15 minutes
  • 30m - 30 minutes
  • 1h - 1 hour
  • 4h - 4 hours
  • 1d - 1 day

Examples from Test Files

Basic Timestamp Operations

Based on /test/date_time_test.go:

-- Create a table with timestamp columns
CREATE TABLE timestamp_test (
    id INTEGER PRIMARY KEY,
    event_time TIMESTAMP
);

-- Insert timestamps in different formats
INSERT INTO timestamp_test VALUES (1, '2023-05-15 14:30:45');
INSERT INTO timestamp_test VALUES (2, '2023-05-15T14:30:45');
INSERT INTO timestamp_test VALUES (3, '2023-05-15');

-- Query with date truncation
SELECT id, DATE_TRUNC('day', event_time) FROM timestamp_test;

Time Series Aggregation

Based on /test/candle_time_trunc_test.go:

-- Aggregating time series data into 15-minute intervals
SELECT 
    TIME_TRUNC('15m', timestamp) AS time_bucket,
    FIRST(open) AS open_price,
    MAX(high) AS high_price,
    MIN(low) AS low_price,
    LAST(close) AS close_price,
    SUM(volume) AS total_volume
FROM candle_data
GROUP BY TIME_TRUNC('15m', timestamp)
ORDER BY time_bucket;

Time Zone Handling

Stoolap normalizes timestamps to UTC internally for consistent storage and comparison. When you provide a timestamp without a timezone specification, it is interpreted as being in UTC.

-- These are equivalent
INSERT INTO events VALUES (1, '2023-05-15T14:30:45Z');
INSERT INTO events VALUES (2, '2023-05-15T14:30:45+00:00');
INSERT INTO events VALUES (3, '2023-05-15 14:30:45'); -- Assumed UTC

Date and Time Comparisons

You can use the standard comparison operators with timestamp values:

-- Equality comparison
SELECT * FROM events WHERE event_date = '2023-05-15';

-- Range queries
SELECT * FROM events 
WHERE event_date >= '2023-05-01' AND event_date < '2023-06-01';

-- Less than
SELECT * FROM events WHERE event_date < NOW();

Indexing Timestamps

Timestamp columns can be indexed for efficient filtering:

-- Create an index on a timestamp column
CREATE INDEX idx_event_date ON events(event_date);

-- This query can now use the index
SELECT * FROM events WHERE event_date >= '2023-05-01';

Limitations

Based on the implementation, Stoolap has the following limitations for date and time handling:

  1. Limited Time Zone Support: While timestamps are normalized to UTC internally, there are no explicit functions for time zone conversion.

  2. No Date/Time Arithmetic: There are no built-in functions for adding or subtracting intervals from dates.

  3. No Date/Time Extraction: Functions to extract parts from dates (like EXTRACT(YEAR FROM date)) are not implemented.

  4. No Custom Formatting: No functions to format timestamps in custom output formats.

Best Practices

  1. Consistent Format Usage: Use ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ) for consistency.

  2. Index Timestamp Columns: If you filter frequently on timestamp columns, create indexes on them.

  3. Use DATE_TRUNC for Date-Only Comparisons: When you need to compare only the date part, use DATE_TRUNC to remove the time component.

  4. Use TIME_TRUNC for Time Series: For time series data, use TIME_TRUNC to bucket data into regular intervals for analysis.

  5. Store in UTC: Always store timestamps in UTC for consistency, especially when working with data across different time zones.