MySQL / SQLite
DATETIME vs TIMESTAMP in MySQL; and SQLite has no real date type at all.
MySQL distinguishes two date-time types. DATETIME stores wall-clock fields (year through seconds) exactly as written, with no time zone attached and a range of 1000-01-01 to 9999-12-31. TIMESTAMP is converted to UTC on write and back to the session’s time_zone on read — semantically closer to an instant — but its range is capped at 1970-01-01 00:00:01 UTC through 2038-01-19 03:14:07 UTC, the classic 32-bit Unix-time ceiling. The year-2038 limit is a hard constraint of the type, not a configuration option.
SQLite takes a radically different approach: it has no dedicated date/time column type. The type affinity system means you can store dates as TEXT (an ISO 8601 string), REAL (a Julian day number), or INTEGER (a Unix timestamp in seconds). SQLite’s built-in date functions (date(), datetime(), strftime(), unixepoch()) understand all three representations, but the database itself does not enforce which one you use. Discipline around format and precision is entirely the application’s responsibility.
Practical guidance
For MySQL, prefer DATETIME when you need a date past 2038 or when the value is genuinely a civil wall-clock time (e.g. a recurring local-time schedule). Use TIMESTAMP for created-at / updated-at fields in applications that will not operate past 2038 and where automatic UTC conversion is useful. For SQLite, TEXT in ISO 8601 format is the most human-readable and the safest to handle across languages; INTEGER Unix seconds trades readability for compactness and arithmetic convenience.
Pitfall: Using MySQL TIMESTAMP for dates after 2038. The column silently stores 0000-00-00 00:00:00 or raises an error for out-of-range values depending on the SQL mode, with no compile-time or schema-level warning that a future date will fail.
Go deeper: SQLite date arithmetic and format pitfalls
SQLite’s date functions treat TEXT values as UTC unless an offset modifier is applied. If your application stores local times as plain ISO 8601 strings (e.g. '2026-06-05 10:00:00') without a Z or offset suffix, SQLite will perform arithmetic on them as if they were UTC — silently wrong if the value was actually in a local zone. The only way to get correct cross-timezone arithmetic is to store UTC explicitly (append Z or convert before insert) or to store Unix epoch integers and use unixepoch() / datetime(..., 'unixepoch').
MySQL TIMESTAMP session-timezone conversion sounds convenient, but it creates subtle bugs: a value inserted in one timezone session reads back differently in another. If your application servers run in different zones, or if you ever change the MySQL server’s time_zone setting, existing TIMESTAMP rows will appear to shift. Storing UTC explicitly in a DATETIME column — and handling timezone conversion in application code — is often the safer long-term choice.