Hi:
During the last few months, we added quite a few columns type TIMESTAMP to record an instant in time when someone created or modified something. But we have some columns DATETIME for similar purposes (audittrail and auditscripts come to mind).
So, should we use one or the other? Having two types for the same concept can cause inconsistencies, so I guess we should move towards one or the other.
Checked with Gemini to get a quick idea:
Core Differences
While both DATETIME and TIMESTAMP store date and time information in MariaDB, they differ fundamentally in timezone handling, storage size, range, and auto-update behavior.
Key Technical Behavior
1. Timezone Handling
DATETIME is timezone-independent. What you insert is what gets stored and retrieved, regardless of server or session settings.
TIMESTAMP normalizes the input to UTC using the current connection's timezone setting. When queried, it converts the UTC value back to the querying session's timezone.
2. The 2038 Problem
TIMESTAMP uses a 32-bit signed integer under the hood (seconds since 1970-01-01 00:00:00 UTC). It will overflow on January 19, 2038.
DATETIME does not suffer from this limitation and safely stores far-future dates up to the year 9999.
When to Use Which
Use DATETIME when:
Storing absolute or historical dates: Birthdays, appointments, future reservations, or historical logs that should not shift if a user views them from a different timezone.
Working with dates beyond 2038: Any long-term projections, financial models, or future scheduling.
Predictable display is required: When you want to guarantee that the value retrieved is identical to the string inserted.
Use TIMESTAMP when:
Tracking record lifecycles: Audit columns like created_at or updated_at.
Managing global multi-region applications: When users in different timezones need events displayed relative to their local time automatically.
Optimizing storage space: When managing massive tables where saving 1 byte per row across hundreds of millions of rows provides meaningful savings.
Hi:
During the last few months, we added quite a few columns type TIMESTAMP to record an instant in time when someone created or modified something. But we have some columns DATETIME for similar purposes (audittrail and auditscripts come to mind).
So, should we use one or the other? Having two types for the same concept can cause inconsistencies, so I guess we should move towards one or the other.
Checked with Gemini to get a quick idea:
Core Differences
While both DATETIME and TIMESTAMP store date and time information in MariaDB, they differ fundamentally in timezone handling, storage size, range, and auto-update behavior.
Key Technical Behavior
1. Timezone Handling
DATETIME is timezone-independent. What you insert is what gets stored and retrieved, regardless of server or session settings.
TIMESTAMP normalizes the input to UTC using the current connection's timezone setting. When queried, it converts the UTC value back to the querying session's timezone.
2. The 2038 Problem
TIMESTAMP uses a 32-bit signed integer under the hood (seconds since
1970-01-01 00:00:00 UTC). It will overflow on January 19, 2038.DATETIME does not suffer from this limitation and safely stores far-future dates up to the year 9999.
When to Use Which
Use
DATETIMEwhen:Storing absolute or historical dates: Birthdays, appointments, future reservations, or historical logs that should not shift if a user views them from a different timezone.
Working with dates beyond 2038: Any long-term projections, financial models, or future scheduling.
Predictable display is required: When you want to guarantee that the value retrieved is identical to the string inserted.
Use
TIMESTAMPwhen:Tracking record lifecycles: Audit columns like
created_atorupdated_at.Managing global multi-region applications: When users in different timezones need events displayed relative to their local time automatically.
Optimizing storage space: When managing massive tables where saving 1 byte per row across hundreds of millions of rows provides meaningful savings.