What is the difference between DATETIME and TIMESTAMP in MySQL?
Why Interviewers Ask This
Candidates at the intermediate level are expected to not only know this concept but explain the trade-offs involved. Interviewers use this question to see if you can reason about design decisions, not just recall facts.
Answer
Both store date and time values, but with important differences: DATETIME: stores date and time literally as entered: "YYYY-MM-DD HH:MM:SS"; range: 1000-01-01 to 9999-12-31; 8 bytes; NOT affected by timezone — stores what you give it, returns what it stored. If you insert "2024-01-15 10:00:00" in New York timezone, it stores and returns exactly that, regardless of the server's timezone. TIMESTAMP: stores as UTC internally (Unix timestamp) and converts to/from the current session timezone on read/write; range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (the 2038 problem!); 4 bytes (more space-efficient); supports automatic initialization and update: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Use TIMESTAMP when: you need timezone-aware storage (display different times to users in different zones); recording when something happened. Use DATETIME when: you store user-facing calendar dates/times that should not change with timezone (appointment slots, event dates); you need dates outside TIMESTAMP's 1970-2038 range. Always store times in UTC in your application layer for maximum clarity.
Common Mistake
Rushing to answer is a common mistake. Take two seconds to structure your response: definition → example → trade-off. This structure makes complex MySQL / SQL answers easy to follow.