Database Storage Best Practices
Proper database storage of dates and times is crucial for data integrity, query performance, and international compatibility. This page covers best practices for storing date and time data in MySQL.
MySQL Date and Time Types
MySQL provides five specialized data types for storing temporal data. Understanding each type's characteristics helps you choose the right one for your application.
| MySQL Type | Stores | Format | Range | Use For |
|---|---|---|---|---|
DATE |
Date only | YYYY-MM-DD |
1000-01-01 to 9999-12-31 | Birthdays, due dates, event dates |
TIME |
Time only | HH:MM:SS |
-838:59:59 to 838:59:59 | Opening hours, duration |
DATETIME |
Date + Time | YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 to 9999-12-31 23:59:59 | Appointments, events, logs |
TIMESTAMP |
Date + Time (UTC) | YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:01 to 2038-01-19 03:14:07 | Record creation/update times |
YEAR |
Year only | YYYY |
1901 to 2155 | Birth year, academic year |
Understanding Each MySQL Type
MySQL DATE Type
Stores date-only values in YYYY-MM-DD format. Requires 3 bytes of storage. Range spans from 1000-01-01 to 9999-12-31. Use DATE when time-of-day is irrelevant, such as birthdays, due dates, or holidays. The compact storage makes it ideal for large datasets where only the date matters.
For additional details, please refer to the MySQL DATE Documentation.
MySQL TIME Type
Stores time-only values or time durations in HH:MM:SS format. Requires 3 bytes of storage. Unusually, TIME supports a wide range from -838:59:59 to 838:59:59, allowing it to represent time differences exceeding 24 hours. Use TIME for store hours, appointment slots, or elapsed time calculations. Negative values are supported for representing time differences.
For more information, please refer to the MySQL TIME Documentation.
MySQL DATETIME Type
Stores date and time together in YYYY-MM-DD HH:MM:SS format without timezone conversion. Requires 8 bytes of storage. Range spans from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. DATETIME stores exactly what you insert - no timezone conversions occur. Use DATETIME for appointments, scheduled events, and any "local time" data. This is the most commonly used type for user-facing date/time data.
For additional guidance, please refer to the MySQL DATETIME Documentation.
MySQL TIMESTAMP Type
Stores date and time in YYYY-MM-DD HH:MM:SS format with automatic timezone conversion. Requires 4 bytes of storage. Limited range from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (the Year 2038 problem). TIMESTAMP stores values in UTC internally and automatically converts to the session's timezone on retrieval. TIMESTAMP is perfect for system timestamps like created_at and updated_at because it automatically updates and handles timezone conversions. However, its limited range makes it unsuitable for birth dates or far-future events.
For more information, please refer to the MySQL TIMESTAMP Documentation.
MySQL YEAR Type
Stores year-only values in YYYY format. Requires just 1 byte of storage. Range from 1901 to 2155. Use YEAR for birth years, academic years, vehicle model years, or any year-only data where the specific date isn't needed. The minimal storage footprint makes it ideal for large datasets.
For additional guidance, please refer to the MySQL YEAR Documentation.
Choosing the Right Type
Use DATE When:
- You only need the date, not the time (birthdays, deadlines, holidays)
- Time of day is irrelevant
- You want to save storage space (3 bytes vs 8 bytes for DATETIME)
Use TIME When:
- You only need the time, not the date (store hours, appointment slots)
- You're storing duration (elapsed time)
- Date context is stored separately or unnecessary
Use DATETIME When:
- You need both date and time (appointments, events, deadlines)
- You're storing "local time" without timezone conversion
- You need a wide date range (beyond 2038)
Use TIMESTAMP When:
- You need automatic timezone conversion (UTC storage)
- You're tracking record creation/modification times
- Your date range is after 1970 and before 2038
DATETIME vs TIMESTAMP: Use DATETIME for user-facing dates/times (appointments, events). Use TIMESTAMP for system timestamps (created_at, updated_at). TIMESTAMP stores in UTC and converts to session timezone; DATETIME stores exactly as entered.
Format Conversions
Understanding how HTML5 input formats map to MySQL formats is essential for proper data handling.
DATE Input (form_date)
No conversion needed - direct mapping:
TIME Input (form_time)
MySQL automatically adds seconds if not provided:
PHP substr(): The substr() function extracts a portion of a string. substr($string, 0, 5) extracts 5 characters starting from position 0, effectively getting "14:30" from "14:30:00". The third parameter is length, not the ending position.
For more information, please refer to the PHP substr() Documentation.
DATETIME-LOCAL Input (form_datetime_local)
Requires conversion between formats:
PHP str_replace(): The str_replace() function replaces all occurrences of a search string with a replacement string. It's case-sensitive and processes the entire string. Format: str_replace($search, $replace, $subject). Perfect for simple find-and-replace operations like swapping 'T' with a space.
For more information, please refer to the PHP str_replace() Documentation.
MONTH Input (form_month)
Store as VARCHAR - no conversion needed:
WEEK Input (form_week)
Store as VARCHAR - no conversion needed:
Model Helper Methods
Create reusable conversion methods in your models to maintain clean, consistent code:
Default Values and NULL Handling
Setting Default Values
MySQL CURRENT_TIMESTAMP: A special function that returns the current date and time. When used as DEFAULT CURRENT_TIMESTAMP, MySQL automatically sets the column to the current timestamp when a new record is inserted. The ON UPDATE CURRENT_TIMESTAMP clause automatically updates the timestamp whenever the record is modified.
For more information, please refer to the MySQL CURRENT_TIMESTAMP Documentation.
Handling NULL vs Empty String
Indexing for Performance
Proper indexing of date and time columns can dramatically improve query performance, especially for range queries and filtering operations.
When to Index Date/Time Columns
MySQL Indexes: An index is a data structure that improves the speed of data retrieval operations. Think of it like a book's index - instead of scanning every page, you can jump directly to relevant content. Indexes are essential for WHERE clauses, JOIN conditions, and ORDER BY operations on large tables. However, indexes consume storage space and slow down INSERT/UPDATE operations slightly.
For more information, refer to the official MySQL Index Optimization Documentation.
Composite Indexes: A composite (or compound) index includes multiple columns. The order of columns matters - MySQL can use the index for queries that filter on the leftmost column(s). For example, an index on (user_id, appointment_date) works for queries filtering by user_id alone, or by both user_id and appointment_date, but not for queries filtering only by appointment_date.
For additional guidance, please refer to the official MySQL Composite Indexes Documentation.
Index Usage Examples
Index Performance Tip: Avoid using functions on indexed date columns in WHERE clauses. When you apply a function like YEAR(), MONTH(), or DATE() to a column, MySQL cannot use the index and must examine every row. Instead of WHERE YEAR(date_column) = 2025, use WHERE date_column BETWEEN '2025-01-01' AND '2025-12-31' to leverage the index.
Querying Date and Time Data
Date Range Queries
MySQL CURDATE(): Returns the current date in YYYY-MM-DD format. Unlike NOW() or CURRENT_TIMESTAMP (which return date and time), CURDATE() returns only the date. It's equivalent to CURRENT_DATE and is useful for date-only comparisons.
For more information and guidance, refer to the MySQL CURDATE() Documentation.
MySQL DATE_ADD(): Adds a time interval to a date. Format: DATE_ADD(date, INTERVAL value unit). Common units: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE. Example: DATE_ADD(CURDATE(), INTERVAL 7 DAY) returns the date 7 days from today. Also supports negative intervals via DATE_SUB().
For additional information, refer to the official MySQL DATE_ADD() Documentation.
Time-Based Queries
MySQL CURTIME(): Returns the current time in HH:MM:SS format (24-hour). Unlike NOW() which returns date and time, CURTIME() returns only the time component. Useful for comparing against TIME columns like business hours. Equivalent to CURRENT_TIME.
For more information, please refer to the official MySQL CURTIME() Documentation.
PHP date(): Formats a timestamp into a readable string using format characters. The format character 'l' (lowercase L) returns the full textual day of the week (e.g., "Monday", "Tuesday"). Common format characters: 'Y' (4-digit year), 'm' (month with leading zero), 'd' (day with leading zero), 'H' (24-hour format), 'i' (minutes), 's' (seconds).
For additional guidance, please refer to the official PHP date() Documentation.
Aggregating by Date Periods
MySQL DATE(), YEAR(), MONTH() Functions: These extract specific components from date/time values. DATE() extracts just the date from a DATETIME. YEAR() extracts the year (returns integer 1000-9999). MONTH() extracts the month (returns integer 1-12). While useful in SELECT and GROUP BY clauses, avoid using them in WHERE clauses on indexed columns as this prevents index usage.
For more information, please refer to the official MySQL Date/Time Functions Documentation.
Timezone Considerations
Understanding timezone behavior is crucial for applications serving users across different time zones.
DATETIME vs TIMESTAMP Timezone Behavior
MySQL Timezone Handling: TIMESTAMP columns automatically convert between UTC (for storage) and the session's timezone (for display). DATETIME stores exactly what you insert with no conversion. For most Trongate applications serving a local audience, DATETIME is simpler and more predictable. Use TIMESTAMP only when you need automatic timezone conversion or for system timestamps like created_at/updated_at.
For additional guidance, please refer to the official MySQL Timezone Documentation.
Application-Level Timezone Handling
PHP DateTime Class: An object-oriented approach to date/time handling. Create instances with new DateTime($string) and format them with ->format($format). The DateTime class is more powerful and reliable than the procedural date() and strtotime() functions, especially for timezone handling, date arithmetic, and parsing complex formats. Methods include modify(), add(), sub(), and setTimezone().
For more information, please refer to the official PHP DateTime Class Documentation.
PHP DateTime::format(): Formats a DateTime object into a string using format characters. In the example, 'F j, Y \a\t g:i A': 'F' = full month name, 'j' = day without leading zero, 'Y' = 4-digit year, '\a\t' = literal "at" (backslashes escape the letters), 'g' = 12-hour format without leading zero, 'i' = minutes with leading zero, 'A' = AM/PM.
For additional guidance, please refer to the PHP DateTime::format() Documentation.
Data Migration and Import
When importing data from external sources, you'll often need to convert various date formats to MySQL's expected format.
Converting External Date Formats
PHP strtotime(): Converts any English textual datetime description into a Unix timestamp. It's very flexible, parsing formats like "12/27/2025", "next Monday", "+7 days", or "2025-12-27 14:30:00". However, strtotime() can be ambiguous with formats like "01/02/2025" (is it January 2 or February 1?). For non-standard or ambiguous formats, use DateTime::createFromFormat() instead.
For more information, refer to the PHP strtotime() Documentation.
PHP DateTime::createFromFormat(): Creates a DateTime object from a string using an explicit format specification. Unlike strtotime() which guesses the format, createFromFormat() requires you to specify exactly how the date is formatted. Format: DateTime::createFromFormat($format, $string). Example: 'd/m/Y' means day/month/year with leading zeros. This is the most reliable way to parse dates in known formats.
For additional guidance, please refer to the PHP DateTime::createFromFormat() Documentation.
Handling Excel Date Imports
Excel Date System: Excel stores dates as serial numbers - the count of days since January 1, 1900 (with a known bug treating 1900 as a leap year). To convert to Unix timestamps, subtract 25569 (days between 1900-01-01 and 1970-01-01) and multiply by 86400 (seconds per day). This conversion is necessary when importing data from Excel spreadsheets. Be aware that Excel also has an alternate 1904 date system used primarily on older Mac versions.
Working with Month Values
When working with month data from form_month(), you'll often need to convert to date ranges or perform month-based queries.
PHP date() 't' Format Character: The 't' format character returns the number of days in the given month (28-31). This automatically accounts for varying month lengths and leap years. For example, date('Y-m-t', strtotime('2025-12-01')) returns "2025-12-31" (December has 31 days), while date('Y-m-t', strtotime('2025-02-01')) returns "2025-02-28" (February 2025 has 28 days).
For more information, please refer to the official PHP documentation regarding PHP date() Format Characters.
Common Pitfalls to Avoid
1. Using VARCHAR Instead of DATE Types
2. Storing Dates as Integers or Timestamps
3. Not Handling NULL Values Properly
4. Using Functions on Indexed Columns
5. Inconsistent Date Formats
Complete Example: Table Design
Performance Optimization Summary
| Practice | Impact | Example |
|---|---|---|
| Use proper date types | High | DATE vs VARCHAR(10) |
| Index date columns in WHERE clauses | High | CREATE INDEX idx_date ON table(date_column) |
| Avoid functions on indexed columns | High | Use BETWEEN instead of YEAR() |
| Use composite indexes for common queries | Medium | INDEX(user_id, date_column) |
| Use DATETIME over TIMESTAMP when possible | Low | Better range, no timezone conversion |
Quick Reference
| Input Type | Form Format | Database Type | Database Format | Conversion Required |
|---|---|---|---|---|
| date | YYYY-MM-DD |
DATE | YYYY-MM-DD |
No |
| time | HH:MM |
TIME | HH:MM:SS |
No (MySQL adds :00) |
| datetime-local | YYYY-MM-DDTHH:MM |
DATETIME | YYYY-MM-DD HH:MM:SS |
Yes (replace T with space, add :00) |
| month | YYYY-MM |
VARCHAR(7) | YYYY-MM |
No |
| week | YYYY-W## |
VARCHAR(8) | YYYY-W## |
No |
Summary
- Use proper MySQL date/time types (DATE, TIME, DATETIME) for validation and performance
- Store dates in ISO 8601 format for international compatibility
- Use NULL for optional dates, not empty strings or '0000-00-00'
- Create indexes on frequently queried date columns
- Avoid using functions on indexed columns in WHERE clauses
- Use DATETIME for most user-facing dates, TIMESTAMP for system timestamps
- Create model helper methods for datetime-local conversions
- Store month and week values as VARCHAR in their native format
- Always validate and sanitize date input on the server side
- Consider timezone implications for global applications
Key Principle: Store dates and times in standardized, database-native formats. This ensures data integrity, enables efficient querying, and maintains international compatibility across your application.
We're continually improving the Trongate documentation. If anything is incorrect, unclear, incomplete, or could be better, we'd genuinely appreciate your input.
Share your thoughts in the Documentation Feedback.