SQLite Date-Time Types: Storage and Query of DATE, TIME, and DATETIME

The DATE type is used to store date values in the format YYYY-MM-DD. By default, it uses the current time (CURRENT_TIMESTAMP) as the inserted value and does not include a time component.

• Storage: SQLite automatically converts dates into integer form (e.g., 1582004800 represents April 1st, 2023 at 00:00:00).
• Querying: Specific dates can be filtered using WHERE date = '2023-04-01', or the DATE() function can be used to extract the date portion from a field (e.g., SELECT DATE(created_at) FROM table).

TIME Type

The TIME type stores time values in the format HH:MM:SS. By default, it uses the current time (CURRENT_TIME) as the inserted value and does not include a date component.

• Storage: SQLite converts times into integer form (e.g., 1582004800 represents April 1st, 2023 at 00:00:00). Note: The example integer in the source text appears to represent a full timestamp rather than just time; however, based on standard SQLite behavior for TIME storage which is seconds since epoch relative to midnight or similar internal representation depending on version context, we translate strictly what implies the conversion mechanism.
• Querying: Specific times can be filtered using WHERE time = '09:15:30', or the TIME() function can be used to extract the time portion from a field (e.g., SELECT TIME(updated_at) FROM table).

DATETIME Type

The DATETIME type combines date and time, formatted as YYYY-MM-DD HH:MM:SS. It supports more precise time operations and defaults to using the current time (CURRENT_TIMESTAMP) for insertion values.

• Storage: SQLite stores DATETIME values in integer form (e.g., 1703424000 represents April 1st, 2023 at 09:15:30).
• Querying: Specific time points can be filtered using WHERE datetime = '2023-04-01 09:15:30', or the DATETIME() function can be used to extract the complete timestamp from a field (e.g., SELECT DATETIME(created_at) FROM table).

Storage and Query Methods

• Storage: When creating tables, column types can be defined using DATE(column), TIME(column), or DATETIME(column).
• Querying: Use SELECT statements to filter date/time fields or restrict ranges via WHERE conditions (e.g., WHERE date BETWEEN '2023-04-01' AND '2023-04-02').
• Example:

  CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME
  );
  
  INSERT INTO events (event_date, event_time, event_datetime) VALUES ('2023-04-01', '09:15:30', '2023-04-01 09:15:30');
  
  SELECT DATE(event_datetime) AS date, TIME(event_datetime) AS time FROM events;
  

Use Cases for Data Types

• DATE: Suitable for scenarios requiring the storage of complete dates (e.g., order creation timestamps).
• TIME: Suitable for scenarios requiring the storage of specific times only (e.g., user login times).
• DATETIME: Suitable for scenarios requiring simultaneous storage of both date and time information (e.g., recording event timestamps).