Back to articles list
- 17 minutes read

MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.

Whenever you need to save datetime data, a question arises about what MySQL type to use. Do you go with a native MySQL DATE type or use an INT field to store date and time info as a plain number?

In this article, I’ll explain MySQL’s native options and give you a comparison table of the most common datatypes. We’ll also benchmark some typical queries and reach some conclusions about which datatype to use in a given situation.

Native MySQL Datetime Datatypes

Datetime data represents a point in time. This could be a log entry, an Internet of Things timestamp, calendar event data, etc. MySQL has two native types that allow us to save this information in a single field: Datetime and Timestamp. Here’s what the MySQL documentation says about these datatypes:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.

The TIMESTAMP data type is used for values that contain both date and time parts.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions.



So, as an example:

CREATE TABLE `datetime_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;

CREATE TABLE `timestamp_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;

Besides the native datetime representations, there is another common approach to storing date and time information. This is to use an INT field to store the Unix time (the number of seconds that have elapsed since the foundation of Coordinated Universal Time (UTC) on January 1, 1970).

MySQL also provides a way to save part of a time representation by using the Date, Year or even Time types. Since this article is about the best way to store an exact point in time, we are not including these less-precise partial types in our discussion.

Using the INT Type with Unix Time

Using a plain INT column type to store Unix time is the most trivial approach. With INT, you can feel certain that the number you’re storing can quickly and reliably be inserted into a table, like this:

INSERT INTO `vertabelo`.`sampletable`
(
 `id`,
 `measured_on` #INT TYPE COLUMN
)
VALUES
(
 1,
 946684801 
 #unix stamp equivalent to 01/01/2000 @ 12:00am (UTC) http://unixtimestamp.com
);

That’s all there is to it. It’s just a plain ol’ INT column and MySQL will treat it as such, using 4 bytes to store that data internally. So if you perform a SELECT on this you will get a number, period. If you would like to make any comparison using this column as a date, the following query wouldn’t work properly:

SELECT 
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    measured_on > '2016-01-01' #measured_on is compared as a string to resolve the query
LIMIT 5;

This is because MySQL sees INT as a number, not a date. In order to make a date comparison, you must either obtain the seconds elapsed up to 2016-01-01 or you will need to use the FROM_UNIXTIME() MySQL function to convert the INT column to a Datetype. The following query demonstrates the use of the FROM_UNIXTIME() function:

SELECT 
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    FROM_UNIXTIME(measured_on) > '2016-01-01'
LIMIT 5;

This will properly retrieve records of dates after 2016-01-01. You can also compare your number directly against the Unix representation of 2016-01-01, which is 1451606400. Doing this means there’s no need to use any special function, since you are comparing straight numbers. Here’s the query:

SELECT 
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
   measured_on > 1451606400
LIMIT 5;

What if it’s simply not efficient or even feasible to make this conversion in advance? For instance, say that you want all the records from every Wednesday in 2016. In order to do this without any MySQL date functions, you’d have to get the Unix timestamp for the start and end date and time for each of the Wednesdays in 2016. Then you’d have to write a huge query that will have at least 104 comparisons in the WHERE. (There are 52 Wednesdays in 2016, and you have to consider the start (0:00 am) and end of the day (11:59:59 pm)...)

The bottom line is that it’s quite probable that you will end up using the FROM_UNIXTIME() conversion function after all. So, why not give the actual date types a try?

Using Datetime and Timestamp

Datetime and Timestamp work pretty much the same way. Both store date and time information with up to six digits precision on fractional seconds. Also, using a human-readable date like ‘2016-01-01’ (to facilitate comparisons) will work. And both formats support “relaxed formatting” when performing queries. Relaxed syntax will permit any punctuation character as the delimiter. For instance, take a string in either YYYY-MM-DD HH:MM:SS or YY-MM-DD HH:MM:SS format. Any of these versions will work in a relaxed formatting situation:

  • 2012-12-31 11:30:45
  • 2012^12^31 11+30+45
  • 2012/12/31 11*30*45
  • 2012@12@31 11^30^45

Other relaxed formats are allowed; you can find them all in the MySQL Reference Manual.

By default, both Datetime and Timestamp formats are retrieved in the standard output format – year-month-day hour:minute:second (e.g. 2016-01-01 23:59:59). If fractional seconds are used, they will come after the seconds as a decimal value (e.g. 2016-01-01 23:59:59.5).

The core differences between Timestamp and Datetime relate primarily to how MySQL represents this information internally: both are stored as binary rather than strings, but Timestamp will use one less byte (four bytes) than Datetime (five bytes) to represent the date/time part. Both will use additional space (1-3 bytes) when saving fractional seconds. If you store 1.5 million records, this 1-byte difference is negligible:

1.5 million records * 1 extra byte per record / (1048576 bytes/MB) = 1.43 MB

Timestamp’s one-byte savings comes at a price: you can only store a value from '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. Datetime, however, allows you to save any value from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999.

An important difference – one that many MySQL developers are not aware of – is that MySQL uses the server time zone to convert a Timestamp value to its UTC equivalent and saves that. It will apply the time zone conversion again when retrieving the value, so you get your “original” date/time value back again. Maybe. Here’s what can happen.

Ideally, if you stay in the same time zone, MySQL will retrieve the same value you stored. In my experience, you can run into issues if your database deals with a change in time zone. For instance, this can happen as a change in the server (e.g. you change your db from a server in Dublin to one in California, or you simply change the time zone in the server). Either way, if your time zone is different when you retrieve your data, the data may be impacted.

Datetime columns are not changed by the db. They will store and retrieve the same value every time, independent from the configured time zone. Personally, I consider this a more consistent option.

From MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, “MySQL Server Time Zone Support”.

Comparison Summary

Before digging into the actual performance difference when using each of these datatypes, let’s consider a summary table that will give you some more insights. The weaknesses of each type are in red.


FeatureDatetimeTimestampInt (store Unix time)
Native time representationYesYesNo, so most operations need a conversion function first, like FROM_UNIXTIME()
Can store fractional secondsYes, up to 6 digits precisionYes, up to 6 digits precisionNo
Valid range'1000-01-01 00:00:00.000000'
to
'9999-12-31 23:59:59.999999
'1970-01-01 00:00:01.000000'
to
'2038-01-19 03:14:07.999999'
If unsigned,
'1970-01-01 00:00:01.000000;
theoretically up to
'2106-2-07 06:28:15'
Auto-initialization (MySQL 5.6.5+)YesYesNo
Relaxed interpretation (MySQL docs)YesYesNo, you must use a proper format
Value is changed to UTC when storedNoYesNo
Can be changed to another typeYes, if the value results in a valid Timestamp rangeYes, alwaysYes, if the value results in a valid range and using a conversion function
Storage requirements (MySQL 5.6.4+)5 bytes (plus up to 3 bytes for fractional seconds, if used)4 bytes (plus up to 3 bytes for fractional seconds, if used)4 bytes (no fractional seconds allowed)
Readable as an actual date without further functionsYesYesNo, you have to format the output
PartitioningYesYes, using the UNIX_TIMESTAMP(); any other expressions involving TIMESTAMP values are not permitted as per MySQL 5.7. Also, note these considerations on partition pruning.Yes, using any valid operation on INTs


Ideally, if you stay in the same time zone, MySQL will retrieve the same value you stored. In my experience, you can run into issues if your database deals with a change in time zone. For instance, this can happen as a change in the server (e.g. you change your db from a server in Dublin to one in California, or you simply change the time zone in the server). Either way, if your time zone is different when you retrieve your data, the data may be impacted.

Datetime columns are not changed by the db. They will store and retrieve the same value every time, independent from the configured time zone. Personally, I consider this a more consistent option.

From MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, “MySQL Server Time Zone Support”.

Comparison Summary

Before digging into the actual performance difference when using each of these datatypes, let’s consider a summary table that will give you some more insights. The weaknesses of each type are in red.

Benchmarking INT, Timestamp, and Datetime Performance

To compare the performance of each of these types, I’m using 1.5 million records (1,497,421 to be more precise) from a weather station network I built. This network collects data every minute. To make these tests replicable, I have removed some private columns so you can run your own tests on this data.

From my original table, I created three versions:

  • The datetimemeasures table uses Datetime in the measured_on column to represent the moment that the weather station record was captured.
  • The timestampmeasures table uses Timestamp for the measured_on column.
  • The inttimestampmeasures uses INT (unsigned) for the measured_on column.

These three tables have exactly the same data; the only difference is the measured_on field type. All tables have an index set on the measured_on column.




Benchmark Tools

To evaluate the performance of these datatypes, I have used two methods. One is based on Sysbench, which its site describes as:

“... a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load.”

This tool is recommended in MySQL documentation.

If you’re using Windows (as I am), you can download a zip file with the executables and the testing queries I used. They were based on a suggested approach for benchmarking.

To run a given test, you can use the following command (insert your own connection parameters):

sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo 
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=sysbench_test_file.lua --num-threads=8 --max-requests=100 run

This works where sysbench_test_file.lua is the test file and has the SQL query from each test pointing to each table.

To further validate the results, I’m also running mysqlslap. Again, from their site:

mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.”

Recall that the important thing from these tests is not the absolute time required. It’s the relative resulting time when running the same query on different datatypes. The testing time of the two benchmark tools doesn’t have to be the same, since each tool works differently. The important thing is the datatype comparison, which will be clear as we go deeper into the tests.

The Benchmark Tests

I’ll use three queries that allow us to evaluate several performance aspects:

  • A time range selection
    • This allows us to perform a direct comparison without using any special date function on the Datetime and Timestamp datatypes.
    • At the same time, we can evaluate the impact of using a date function in the INT type column against using a simple numerical comparison on this field. We’ll need to convert the range to Unix numbers for this.
  • A date function selection:
    • This makes it possible to evaluate the performance of using a date function as part of the “WHERE” clause, as opposed to the previous test where the comparison was performed against a plain DATE value.
    • We can also test a scenario where we’re required to use a function to convert the INT column to a valid DATE type and then perform the query.
  • A count() query
    • As an addendum to the previous query, this will evaluate how a typical summarization query performs with our three different representation types.

We’ll cover some common scenarios with these tests, and we will see how performance fares with all three types.

About SQL_NO_CACHE

When using SQL_NO_CACHE in a query, the server does not use the query cache. It neither checks the query cache to see whether the result is already there nor stores the query result. Therefore, each query will reflect the actual performance impact as when it was called for the first time.

Test 1: Select Values in a Range of Dates

This query returns 75,706 rows from the 1,497,421 available measures.

Query 1 with DATETIME:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < '2016-02-01 00:00:00.0';

Performance
Response time (ms)Sysbenchmysqlslap
Min152296
Max12613203
Average362809


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=datetime.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.datetimemeasures m WHERE m.measured_on > 
'2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 
00:00:00.0'" --host=localhost --user=root --concurrency=8 
--iterations=100  --no-drop --create-schema=vertabelo

Query 1 with Timestamp:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < '2016-02-01 00:00:00.0';

Performance
Response time (ms)Sysbenchmysqlslap
Min214359
Max13893313
Average4311004


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=timestamp.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.timestampmeasures m WHERE m.measured_on > 
'2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 
00:00:00.0'" --host=localhost --user=root --concurrency=8 
--iterations=100  --no-drop --create-schema=vertabelo

Query 1 with INT:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0'
        AND FROM_UNIXTIME(m.measured_on) < '2016-02-01 00:00:00.0';

Performance
Response time (ms)Sysbenchmysqlslap
Min24727968
Max655410312
Average41078527


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=int.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.inttimestampmeasures m WHERE FROM_UNIXTIME(m.measured_on) 
> '2016-01-01 00:00:00.0' AND FROM_UNIXTIME(m.measured_on) <
'2016-02-01 00:00:00.0'" --host=localhost --user=root 
--concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

Alternative Query 1 with INT:

As this is a fairly straightforward range search and the dates in the query can be easily translated to a simple numeric comparison, I have included it in this test. It turns out to be the fastest (as you may have anticipated), since it’s only comparing numbers and doesn’t use any date conversion functions:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    m.measured_on > 1451617200
        AND m.measured_on < 1454295600;

Performance
Response time (ms)Sysbenchmysqlslap
Min88171
Max2752157
Average165514


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=basic_int.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.inttimestampmeasures m WHERE m.measured_on > 1451617200 
AND m.measured_on < 1454295600" --host=localhost --user=root 
--concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

TEST 1 SUMMARY
Avg response time (ms)SysbenchSpeed Relative to DatetimemysqlslapSpeed Relative to Datetime
Datetime362-809-
Timestamp43119% slower100424% slower
INT41071134% slower85271054% slower
Alternative INT query16555% faster51436% faster


Both benchmark tools show that Datetime is faster than Timestamp and INT. But Datetime is not faster than using a simple numeric comparison, as we used in the alternative INT query.

Test 2: Select Values Generated on Mondays

This query returns 221,850 rows from the 1,497,421 available measures.

Query 2 with Datetime:

SELECT SQL_NO_CACHE measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min18744343
Max61687797
Average31276103


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=datetime_1.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0" 
--host=localhost --user=root --concurrency=8 --iterations=25  
--no-drop --create-schema=vertabelo

Query 2 with Timestamp:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min26885953
Max666613531
Average36538412


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=timestamp_1.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0" 
--host=localhost --user=root --concurrency=8 --iterations=25  
--no-drop --create-schema=vertabelo

Query 2 with INT:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min20515844
Max700710469
Average34868088


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=int_1.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM
vertabelo.inttimestampmeasures m WHERE 
WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0" --host=localhost 
--user=root --concurrency=8 --iterations=25  --no-drop 
--create-schema=vertabelo

TEST 2 SUMMARY
Avg response time (ms)SysbenchSpeed Relative to DatetimemysqlslapSpeed Relative to Datetime
Datetime3127-6103-
Timestamp365317% slower841238% slower
INT348611% slower808832% slower


Again, in both benchmark tools Datetime is faster than Timestamp and INT. But in this test, the INT query – even though it uses a function to convert the date – gets results faster than the Timestamp query.

Test 3: Select the Number of Measures Generated on Mondays

This query returns one row with the count of measures happening on Mondays (from 1,497,421 available measures).

Query 3 with Datetime:

SELECT SQL_NO_CACHE 
	COUNT(measured_on)
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min17204063
Max45947812
Average27975540


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=datetime_1_count.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on)
FROM vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0"
--host=localhost --user=root --concurrency=8 --iterations=25  
--no-drop --create-schema=vertabelo

Query 3 with Timestamp:

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min19074578
Max543710235
Average34087102


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=timestamp_1_count.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on)
FROM vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0"
--host=localhost --user=root --concurrency=8 --iterations=25  
--no-drop --create-schema=vertabelo

Query 3 with INT:

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Performance
Response time (ms)Sysbenchmysqlslap
Min21085609
Max47649735
Average33077416


Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo
--MySQL-user=root --MySQL-host=localhost --MySQL-password= 
--test=int_1_count.lua --num-threads=8 --max-requests=100 run

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on)
FROM vertabelo.inttimestampmeasures m WHERE 
WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0" --host=localhost 
--user=root --concurrency=8 --iterations=25  --no-drop 
--create-schema=vertabelo

TEST 3 SUMMARY
Avg response time (ms)SysbenchSpeed Relative to DatetimemysqlslapSpeed Relative to Datetime
Datetime2797-5540-
Timestamp340822% slower710228% slower
INT330718% slower741633% slower


Again, both benchmark tools show that Datetime is faster than Timestamp and INT. It’s not conclusive if INT is faster than Timestamp, since mysqlslap showed a slightly faster response for Timestamp and Sysbench showed the reverse.

Note: All tests were run locally in a Windows 10 machine with a dual-core i7 CPU and 16GB RAM, running a MariaDb v10.1.9 engine using innoDb.

Conclusion

Based on this data, I believe Datetime is the best choice in most scenarios. Here’s why:

  • It’s faster (according to our three benchmarks).
  • It is human-readable without any conversion.
  • There are no problems due to time zone switching.
  • It uses only 1 byte more than its counterparts.
  • It allows for a greater date range (from year 1000 to 9999).

If you are just storing a Unix timestamp (and you are within its valid date range), and you really don’t plan to use any date-based queries on it, I’d say it’s okay to use INT. We have seen that it performs simple numeric comparison queries very quickly as you’d be working with a plain number.

What about Timestamp? If the advantages that Datetime has over Timestamp don’t apply to your particular scenario, you may be better off using timestamps. After reading this article, you’ll have a better understanding about the differences between all three types and can make the best choice for your needs.

go to top