SQL Date and Time Data Types: DATE, DATETIME, and TIMESTAMP

Every e-commerce operation runs on dates. You need to know when an order was placed, how long delivery took, which customers have not logged in recently, and how revenue trends month over month. Date columns power all of that, and understanding how they are stored and queried is one of the most practical skills you will build as an analyst.

DATE: Just the Calendar Date

DATE stores a year, month, and day with no time component. The standard format is YYYY-MM-DD.

Use DATE when time of day does not matter. Customer date_of_birth and employee hire_date are good examples. You care about the date, not the hour.

-- Find employees hired in 2023
SELECT e.first_name, e.last_name, e.role, e.hire_date
FROM employees e
WHERE e.hire_date >= '2023-01-01'
  AND e.hire_date < '2024-01-01'
ORDER BY e.hire_date;

-- Calculate approximate customer age from date_of_birth
SELECT
    c.first_name,
    c.last_name,
    c.date_of_birth,
    (STRFTIME('%Y', 'now') - STRFTIME('%Y', c.date_of_birth)) AS approx_age
FROM customers c
WHERE c.date_of_birth IS NOT NULL
ORDER BY c.date_of_birth;

DATETIME: Date Plus Time

DATETIME stores both the date and the time of day. The standard format is YYYY-MM-DD HH:MM:SS.

Columns like created_at, last_login, order_date, shipped_date, and payment_date are all natural DATETIME columns. The exact moment something happened matters, not just the day.

-- Find orders placed in the afternoon on a specific date
SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
FROM orders o
WHERE o.order_date >= '2024-06-15 12:00:00'
  AND o.order_date <  '2024-06-15 24:00:00'
ORDER BY o.order_date;

-- Find customers who logged in within the last 30 days
SELECT c.customer_id, c.first_name, c.last_name, c.last_login
FROM customers c
WHERE c.last_login >= DATE('now', '-30 days')
ORDER BY c.last_login DESC;

TIMESTAMP: Date, Time, and Timezone Awareness

TIMESTAMP looks like DATETIME in format but carries timezone context in databases like PostgreSQL and MySQL. A TIMESTAMP value is stored in UTC and converted to the local session timezone when retrieved. DATETIME stores the value exactly as-is.

In practice, created_at columns in most schemas are TIMESTAMP or DATETIME depending on the database. In SQLite, both are stored as TEXT and behave the same way.

-- List recent payments with full datetime precision
SELECT
    pay.payment_id,
    pay.order_id,
    pay.payment_date,
    pay.amount,
    pay.method,
    pay.status
FROM payments pay
WHERE pay.payment_date >= '2024-01-01'
ORDER BY pay.payment_date DESC
LIMIT 20;

-- Extract just the date part from a datetime column
SELECT
    o.order_id,
    DATE(o.order_date) AS order_date_only,
    o.total_amount
FROM orders o
ORDER BY order_date_only DESC
LIMIT 10;

How SQLite Stores Date and Time

SQLite has no dedicated date or time storage class. It uses one of three approaches depending on how the data was inserted:

| Storage format | Example | SQLite type | |----------------|---------|-------------| | ISO 8601 text | '2024-03-15 14:30:00' | TEXT | | Julian day number | 2460385.10417 | REAL | | Unix timestamp | 1710509400 | INTEGER |

The vast majority of real-world SQLite databases use ISO 8601 text format. The built-in date functions DATE(), DATETIME(), STRFTIME(), JULIANDAY(), and NOW() all understand this format.

-- Common SQLite date function examples using orders
SELECT
    o.order_id,
    o.order_date,
    DATE(o.order_date)                          AS date_only,
    STRFTIME('%Y', o.order_date)                AS order_year,
    STRFTIME('%m', o.order_date)                AS order_month,
    STRFTIME('%Y-%m', o.order_date)             AS year_month,
    JULIANDAY(o.shipped_date) -
        JULIANDAY(o.order_date)                 AS days_to_ship
FROM orders o
WHERE o.shipped_date IS NOT NULL
LIMIT 10;

Filtering and Comparing Dates

Because SQLite dates are stored as text in ISO format, you compare them using standard string literals. Use >= and < instead of BETWEEN when you want to avoid ambiguity on the upper bound.

-- Orders placed in Q1 2024
SELECT o.order_id, o.order_date, o.total_amount, o.status
FROM orders o
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2024-04-01'
ORDER BY o.order_date;

-- Customers created this year
SELECT c.customer_id, c.first_name, c.last_name, c.created_at
FROM customers c
WHERE STRFTIME('%Y', c.created_at) = STRFTIME('%Y', 'now')
ORDER BY c.created_at DESC;

Common Mistakes

-- wrong
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date = '2024-06-15';

-- correct
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date >= '2024-06-15'
  AND o.order_date <  '2024-06-16';

If order_date stores a full datetime like 2024-06-15 09:30:00, comparing with = to a date-only string returns no rows. Filter with a range to capture all times within that day.

---

-- wrong
SELECT o.order_id, JULIANDAY(o.shipped_date - o.order_date) AS days_to_ship
FROM orders o;

-- correct
SELECT o.order_id, JULIANDAY(o.shipped_date) - JULIANDAY(o.order_date) AS days_to_ship
FROM orders o
WHERE o.shipped_date IS NOT NULL;

You cannot subtract date strings directly. Use JULIANDAY() on each column separately and subtract the results to get the difference in days.

---

-- wrong
SELECT c.first_name, c.date_of_birth
FROM customers c
WHERE c.date_of_birth > 1990;

-- correct
SELECT c.first_name, c.date_of_birth
FROM customers c
WHERE c.date_of_birth > '1990-12-31';

Comparing a text date column to a bare integer produces wrong results. Always use a quoted ISO format string when filtering date columns.

Quick Recap

• DATE stores only the calendar date. DATETIME stores date and time. TIMESTAMP adds timezone awareness in databases like PostgreSQL and MySQL.
• SQLite stores all date and time values as TEXT in ISO 8601 format, as REAL Julian day numbers, or as INTEGER Unix timestamps. ISO text is the most common format you will encounter.
• Use STRFTIME() and JULIANDAY() for date extraction and arithmetic in SQLite. Always quote date literals in ISO format and use range comparisons instead of equality when filtering datetime columns.

SELECT o.order_id, o.order_date, o.total_amount FROM orders o WHERE STRFTIME('%Y', o.order_date) = '2024' ORDER BY o.order_date ASC;

SELECT o.order_id, o.order_date, o.shipped_date, ROUND(JULIANDAY(o.shipped_date) - JULIANDAY(o.order_date), 0) AS days_to_ship FROM orders o WHERE o.shipped_date IS NOT NULL AND (JULIANDAY(o.shipped_date) - JULIANDAY(o.order_date)) > 3 ORDER BY days_to_ship DESC;

SELECT STRFTIME('%Y-%m', o.order_date) AS year_month, COUNT(o.order_id) AS total_orders, ROUND(SUM(o.total_amount), 2) AS monthly_revenue FROM orders o WHERE STRFTIME('%Y', o.order_date) = '2024' AND o.status = 'Delivered' GROUP BY year_month ORDER BY year_month ASC;