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:

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.

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.

Feature	Datetime	Timestamp	Int (store Unix time)
Native time representation	Yes	Yes	No, so most operations need a conversion function first, like FROM_UNIXTIME()
Can store fractional seconds	Yes, up to 6 digits precision	Yes, up to 6 digits precision	No
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+)	Yes	Yes	No
Relaxed interpretation (MySQL docs)	Yes	Yes	No, you must use a proper format
Value is changed to UTC when stored	No	Yes	No
Can be changed to another type	Yes, if the value results in a valid Timestamp range	Yes, always	Yes, 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 functions	Yes	Yes	No, you have to format the output
Partitioning	Yes	Yes, 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.

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)	Sysbench	mysqlslap
Min	152	296
Max	1261	3203
Average	362	809

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)	Sysbench	mysqlslap
Min	214	359
Max	1389	3313
Average	431	1004

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)	Sysbench	mysqlslap
Min	2472	7968
Max	6554	10312
Average	4107	8527

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)	Sysbench	mysqlslap
Min	88	171
Max	275	2157
Average	165	514

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)	Sysbench	Speed Relative to Datetime	mysqlslap	Speed Relative to Datetime
Datetime	362	-	809	-
Timestamp	431	19% slower	1004	24% slower
INT	4107	1134% slower	8527	1054% slower
Alternative INT query	165	55% faster	514	36% 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)	Sysbench	mysqlslap
Min	1874	4343
Max	6168	7797
Average	3127	6103

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)	Sysbench	mysqlslap
Min	2688	5953
Max	6666	13531
Average	3653	8412

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)	Sysbench	mysqlslap
Min	2051	5844
Max	7007	10469
Average	3486	8088

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)	Sysbench	Speed Relative to Datetime	mysqlslap	Speed Relative to Datetime
Datetime	3127	-	6103	-
Timestamp	3653	17% slower	8412	38% slower
INT	3486	11% slower	8088	32% 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)	Sysbench	mysqlslap
Min	1720	4063
Max	4594	7812
Average	2797	5540

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)	Sysbench	mysqlslap
Min	1907	4578
Max	5437	10235
Average	3408	7102

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)	Sysbench	mysqlslap
Min	2108	5609
Max	4764	9735
Average	3307	7416

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)	Sysbench	Speed Relative to Datetime	mysqlslap	Speed Relative to Datetime
Datetime	2797	-	5540	-
Timestamp	3408	22% slower	7102	28% slower
INT	3307	18% slower	7416	33% 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.