Describe the difference between DATETIME and TIMESTAMP data types.

The main differences between DATETIME and TIMESTAMP in most SQL databases (e.g., MySQL) are as follows:

• Storage:

  • DATETIME stores year, month, day, hour, minute, second.
  • TIMESTAMP stores the number of seconds since the Unix epoch (January 1, 1970 UTC).

• Value Range:

  • DATETIME generally has a wider date range (e.g., from '1000-01-01 00:00:00' to '9999-12-31 23:59:59').
  • TIMESTAMP has a limited range, depending on 32-bit or 64-bit representation, usually up to the year 2038 for 32-bit.

• Time Zone:

  • DATETIME is independent of time zone and stores the exact entered value.
  • TIMESTAMP is converted from the current time zone to UTC when stored and back when retrieved, so the value depends on the client's time zone.

• Size:

  • Storage size may differ slightly, but often TIMESTAMP is smaller (4 bytes) compared to DATETIME (8 bytes).

• Automatic Update:

  • TIMESTAMP can be configured to automatically update when the row changes (if default or ON UPDATE CURRENT_TIMESTAMP is used), which is convenient for fields tracking the last modification time. DATETIME does not have this built-in feature (though it can be achieved with triggers).

Comparison table:

Characteristic	DATETIME	TIMESTAMP
Storage format	Year, month, day, hour, minute, second	Seconds since Unix epoch (UTC)
Time zone	Independent of time zone	Depends on client's time zone
Value range	Usually wider, up to year 9999	Limited, usually up to 2038 (for 32-bit)
Automatic update	No (built-in)	Yes (optional, ON UPDATE CURRENT_TIMESTAMP)
Typical size	8 bytes	4 bytes

Example SQL for creating tables:

-- Example table with DATETIME
CREATE TABLE events_datetime (
    id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_time DATETIME
);

-- Example table with TIMESTAMP
CREATE TABLE events_timestamp (
    id INT PRIMARY KEY,
    event_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Automatic creation time
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Automatic update on change
);