{"id":106629,"date":"2025-06-03T07:01:00","date_gmt":"2025-06-03T07:01:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106629"},"modified":"2025-05-07T19:20:35","modified_gmt":"2025-05-07T19:20:35","slug":"mysql-vs-postgresql-date-and-time-data-types","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-vs-postgresql-date-and-time-data-types\/","title":{"rendered":"MySQL vs. PostgreSQL: Date and Time Data Types"},"content":{"rendered":"\n

Date and time data types are important because they help track and manage time-based information in databases. This includes things like recording when orders are placed, scheduling events, or calculating the time between two activities. In this article, we will look at differences between how PostgreSQL and MySQL stores and utilizes date and time datatypes.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n \n<\/p>\n\n\n\n

MySQL and PostgreSQL offer several data types that can be used for handling dates and times. These data types provide the tools to store and manage information like dates of a particular event, timestamps, and even time durations. While they both share some similarities on how they handle date and time, there are key differences in how they handle precision, time zones, and date\/time calculations.<\/p>\n\n\n\n

Getting date and time data right is key for keeping databases accurate and useful. In this article, we will compare how MySQL and PostgreSQL handle date and time data, their differences, strengths, and which one might work better for your needs. By the end, you\u2019ll have a clearer idea of which database to choose for managing date and time information.<\/p>\n\n\n\n

<\/a>MySQL Date and Time Data Types<\/h2>\n\n\n\n

MySQL is quite flexible when it comes to accepting date and time values. It provides five main date and time data types, which are, DATE<\/code>, DATETIME<\/code>, TIMESTAMP<\/code>, TIME<\/code>, and YEAR<\/code>. However, these data types must follow a specific accepted format to ensure the values are correctly stored in the database. Here’s a breakdown of the format MySQL expects for its following date and time values.<\/p>\n\n\n\n

Data Type<\/strong><\/p><\/td>

Expected Format<\/strong><\/p><\/td>

Example Values<\/strong><\/p><\/td>

Storage size<\/strong><\/p><\/td><\/tr>

DATE<\/code><\/p><\/td>

YYYY-MM-DD<\/p><\/td>

2025-02-14<\/p><\/td>

3 bytes<\/p><\/td><\/tr>

DATETIME<\/code><\/p><\/td>

YYYY-MM-DD HH:MM:SS<\/p><\/td>

2025-02-14 10:30:45<\/p><\/td>

8 bytes<\/p><\/td><\/tr>

TIMESTAMP<\/code><\/p><\/td>

YYYY-MM-DD HH:MM:SS<\/p><\/td>

2025-02-14 10:30:45<\/p><\/td>

4 bytes<\/p><\/td><\/tr>

TIME<\/code><\/p><\/td>

HH:MM:SS<\/p><\/td>

10:30:45<\/p><\/td>

3 bytes<\/p><\/td><\/tr>

YEAR<\/code><\/p><\/td>

YYYY<\/p><\/td>

2025<\/p><\/td>

1 byte<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

You are also allowed to include fractional seconds (up to 6 digits) into the DATETIME<\/code> and TIMESTAMP<\/code> values. Values are enclosed in quoted strings during insertion. Here is an example of what this would look like:<\/p>\n\n\n\n

CREATE TABLE events (\n    event_id INT AUTO_INCREMENT PRIMARY KEY,\n    event_time DATETIME NOT NULL,  -- Stores exact date and time\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Auto-stores insert time in UTC\n);<\/pre><\/div>\n\n\n\n
Then create a value using an INSERT<\/code> statement:<\/p>\n\n\n\n
INSERT INTO events (event_time) VALUES ('2025-02-14 10:30:45.123456');<\/pre><\/div>\n\n\n\n
If you insert a value that doesn\u2019t follow the expected format, MySQL handles it based on the sql_mode<\/code> setting. In the STRICT_TRANS_TABLES<\/code> mode, dates are checked strictly. You can check the setting value using:<\/p>\n\n\n\n
SHOW VARIABLES LIKE 'sql_mode';<\/pre><\/div>\n\n\n\n
By default, this will look something like this:<\/p>\n\n\n\n
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'<\/pre><\/div>\n\n\n\n
And if there is not a value \u2018STRICT_TRANS_TABLES<\/code>\u2019 (which there is in my example) then you can set it for your connection using the following statement (which will preserve all other settings):<\/p>\n\n\n\n
set sql_mode = CONCAT(@@sql_mode,',STRICT_TRANS_TABLES');<\/pre><\/div>\n\n\n\n
Note: Beware that sql_mode<\/code> pertains to more than just date format handling, so check out this article<\/a> in the MySQL docs before using.<\/p>\n\n\n\n
When MySQL encounters an invalid date format, it does the following:<\/p>\n\n\n\n
Strict Mode (STRICT_TRANS_TABLES):<\/strong> If strict mode is enabled (default in newer MySQL versions), MySQL rejects invalid dates and throws an error. For example:<\/p>\n\n\n\n
INSERT INTO events (event_time) VALUES ('2025-14-02 10:30:45');<\/pre><\/div>\n\n\n\n
MySQL will throw the following error: <\/p>\n\n\n\n
SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: ‘2025-14-02 10:30:45’ for column ‘event_time’ at row 1<\/p>\n\n\n\n
The error occurred because ‘2025-14-02’ is an invalid date format. In MySQL, a valid DATE format follows YYYY-MM-DD, and the month (14) exceeds the valid range (1-12).<\/p>\n\n\n\n
Non-strict mode<\/strong>: If strict mode is disabled, MySQL does not throw an error for invalid dates. Instead, it stores '0000-00-00<\/code>‘ for invalid dates instead of rejecting them. Here’s an example:<\/p>\n\n\n\n
<\/p>\n\n\n\n
-- Remove strict mode setting\nSET sql_mode=(SELECT REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''));<\/pre><\/div>\n\n\n\n
Then execute the statement:<\/p>\n\n\n\n
INSERT INTO events (event_time) VALUES ('2025-14-02'); <\/pre><\/div>\n\n\n\n
You should get back no error message and the data is seemingly stored. But let\u2019s run a SELECT<\/code> statement to see how it stored our date value.<\/p>\n\n\n\n
SELECT * FROM events;<\/pre><\/div>\n\n\n\n
This will return something like:<\/p>\n\n\n\n
event_id|event_time         |created_at\n--------+-------------------+-------------------+\n       1|2025-02-14 10:30:45|2025-04-29 11:12:31|\n       2|0000-00-00 00:00:00|2025-04-29 11:13:58|<\/pre><\/div>\n\n\n\n
The second row is the one where we had strict mode turned off.<\/p>\n\n\n\n
Note that MySQL also allows inserting partial or unknown dates by setting the day or month to 00. This is controlled by the NO_ZERO_IN_DATE<\/code> sql mode. (When setting strict mode earlier, you may have seen a warning about a few settings that are going to be a part of strict mode in MySQL. As such, NO_ZERO_IN_DATE is deprecated<\/a> and will be rolled into strict mode in the future.)<\/p>\n\n\n\n
This can be useful for cases where only part of the date is known. Here\u2019s an example of how this works:<\/p>\n\n\n\n
-- REMOVE NO_ZERO_IN_DATE setting\nSET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));\n\nINSERT INTO events (event_time) VALUES ('2025-00-15'); -- Month unknown\n\nINSERT INTO events (event_time) VALUES ('2025-02-00'); -- Day unknown\n\nselect * from events;<\/pre><\/div>\n\n\n\n
You can see two new rows, each with 00 for a day or month value for event_id<\/code> = 3 and 4:<\/p>\n\n\n\n
 event_id|event_time         |created_at         |\n--------+-------------------+-------------------+\n       1|2025-02-14 10:30:45|2025-04-29 11:12:31|\n       2|0000-00-00 00:00:00|2025-04-29 11:13:58|\n       3|2025-00-15 00:00:00|2025-04-29 11:17:45|\n       4|2025-02-00 00:00:00|2025-04-29 11:17:48|<\/pre><\/div>\n\n\n\n
Some MySQL functions do not work correctly with 0 values. For example, adding an interval to a 0 date can produce unexpected results. For example:<\/p>\n\n\n\n
SELECT DATE_ADD('2025-00-15', INTERVAL 1 MONTH);<\/pre><\/div>\n\n\n\n
This returns a NULL<\/code> value. So, it is not advisable to use this instead, you can enforce valid dates and disallow 0 values, by enabling strict mode and NO_ZERO_DATE<\/code> in sql_mode<\/code>.<\/p>\n\n\n\n
set sql_mode = CONCAT(@@sql_mode,',STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE');<\/pre><\/div>\n\n\n\n
This ensures that MySQL rejects incomplete dates instead of storing them as 0000-00-00 or allowing 0 for days and months and leaves any other settings intact.<\/p>\n\n\n\n
<\/a>When to Use DATETIME vs. TIMESTAMP<\/h3>\n\n\n\n
The DATETIME<\/code> and TIMESTAMP<\/code> data types in MySQL both store date and time values, however, they have distinct differences in their storage and range of values. The DATETIME<\/code> data type takes 8 bytes to store a value while TIMESTAMP<\/code> uses only 4 bytes, making it more space-efficient.<\/p>\n\n\n\n
DATETIME<\/code> can store dates from 1000-01-01<\/code> to 9999-12-31<\/code>, making it suitable for historical records and long-term data while TIMESTAMP<\/code> only works from 1970-01-01<\/code> to 2038-01-19<\/code>, due to its Unix timestamp limitation. If you need to store dates beyond 2038, use the DATETIME<\/code> data type.<\/p>\n\n\n\n
Note: 2038 is not that far in the future, and if there will be a simple fix is not obvious (at best, if the TIMESTAMP is redefined to include later dates, you may have to convert all of your data, so it may be advised to use DATETIME in future development, especially where future dates may be needed.)<\/em><\/p>\n\n\n\n
Below is a breakdown of the major differences between the DATETIME<\/code> and TIMESTAMP<\/code> data type in MySQL:<\/p>\n\n\n\n
Feature<\/strong><\/p><\/td>
DATETIME<\/strong><\/p><\/td>
TIMESTAMP<\/strong><\/p><\/td><\/tr>
Stores Date & Time?<\/p><\/td>
Yes<\/p><\/td>
Yes<\/p><\/td><\/tr>
Time Zone Affected?<\/p><\/td>
No (Stored as-is)<\/p><\/td>
Yes (Stored in UTC, converted to session time zone)<\/p><\/td><\/tr>
Storage size<\/p><\/td>
8 bytes<\/p><\/td>
4 bytes<\/p><\/td><\/tr>
Range<\/p><\/td>
1000-01-01 00:00:00.000000′ to ‘9999-12-31 23:59:59.499999<\/p><\/td>
1970-01-01 00:00:01.000000′ to ‘2038-01-19 03:14:07.499999<\/p><\/td><\/tr>
Best Use Case<\/p><\/td>
When time zone conversion is not needed<\/p><\/td>
When you need timezone-aware timestamps<\/p><\/td><\/tr>
Fractional Seconds?<\/p><\/td>
Yes (supports up to 6 decimal places)<\/p><\/td>
Yes (supports up to 6 decimal places)<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Here\u2019s an example with both Data Types:<\/p>\n\n\n\n
CREATE TABLE product (\n    event_id INT AUTO_INCREMENT PRIMARY KEY,\n    event_name VARCHAR(100),\n    event_time DATETIME,   -- Stores exact input\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Adjusts for time zones\n);\n\nINSERT INTO product (event_name, event_time)\nVALUES\n    ('Conference', '2025-02-14 10:30:45'),\n    ('Webinar', '2025-03-10 15:00:00'),\n    ('Product Launch', '2025-04-01 09:45:00');\n\nSELECT * FROM product;<\/pre><\/div>\n\n\n\n
This returns:<\/p>\n\n\n\n
event_id|event_name    |event_time         |created_at         |\n--------+--------------+-------------------+-------------------+\n       1|Conference    |2025-02-14 10:30:45|2025-04-29 11:30:34|\n       2|Webinar       |2025-03-10 15:00:00|2025-04-29 11:30:34|\n       3|Product Launch|2025-04-01 09:45:00|2025-04-29 11:30:34|<\/pre><\/div>\n\n\n\n
In the output, the event_time<\/code> column shows the exact date and time you entered while the created_at<\/code> <\/strong>column records when the event was added, storing it in UTC and adjusting automatically based on your time zone when retrieved.<\/p>\n\n\n\n
So, if you care about time zone conversions, use TIMESTAMP<\/code> and if you want a fixed value across all time zones, use DATETIME<\/code>.<\/p>\n\n\n\n
<\/a>Handling 2 Digits Years in MySQL<\/h3>\n\n\n\n
MySQL lets you insert years using just two digits, but it automatically expands them to four digits based on a specific rule:<\/p>\n\n\n
\n