Which MySQL Time Type Is Fastest? Benchmarking datetime, timestamp, and bigint

In a database you can use datetime, bigint, or timestamp to represent time; which type is most suitable for storing time?

Data Preparation

Insert 500,000 rows into the database via a program.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_date` datetime NOT NULL,
  `time_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_long` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `time_long` (`time_long`),
  KEY `time_timestamp` (`time_timestamp`),
  KEY `time_date` (`time_date`)
) ENGINE=InnoDB AUTO_INCREMENT=500003 DEFAULT CHARSET=latin1;

The columns time_long, time_timestamp, and time_date store the same moment in different formats.

Entity Class Users

@Builder
@Data
public class Users {
    /** Auto‑increment unique id */
    private Long id;
    /** date type */
    private Date timeDate;
    /** timestamp type */
    private Timestamp timeTimestamp;
    /** long type */
    private long timeLong;
}

DAO Interface

@Mapper
public interface UsersMapper {
    @Insert("insert into users(time_date, time_timestamp, time_long) value(#{timeDate}, #{timeTimestamp}, #{timeLong})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int saveUsers(Users users);
}

Test Class Inserting Data

public class UsersMapperTest extends BaseTest {
    @Resource
    private UsersMapper usersMapper;

    @Test
    public void test() {
        for (int i = 0; i < 500000; i++) {
            long time = System.currentTimeMillis();
            usersMapper.saveUsers(Users.builder()
                .timeDate(new Date(time))
                .timeLong(time)
                .timeTimestamp(new Timestamp(time))
                .build());
        }
    }
}

SQL Query Performance Test

Query by datetime:

select count(*) from users where time_date >= "2018-10-21 23:32:44" and time_date <= "2018-10-21 23:41:22"

Time: 0.171 s

Query by timestamp:

select count(*) from users where time_timestamp >= "2018-10-21 23:32:44" and time_timestamp <= "2018-10-21 23:41:22"

Time: 0.351 s

Query by bigint:

select count(*) from users where time_long >= 1540135964091 and time_long <= 1540136482372

Time: 0.130 s

Conclusion: Under InnoDB, range queries perform fastest with bigint, then datetime, then timestamp.

SQL Group‑By Performance Test

Group by datetime:

select time_date, count(*) from users group by time_date

Time: 0.176 s

Group by timestamp:

select time_timestamp, count(*) from users group by time_timestamp

Time: 0.173 s

Conclusion: Under InnoDB, grouping by timestamp is slightly faster than datetime, but the difference is small.

SQL Order‑By Performance Test

Order by datetime: select * from users order by time_date Time: 1.038 s

Order by timestamp: select * from users order by time_timestamp Time: 0.933 s

Order by bigint: select * from users order by time_long Time: 0.775 s

Conclusion: Under InnoDB, ordering by bigint is fastest, followed by timestamp, then datetime.

Summary

If you need to operate on time fields (range queries, sorting, etc.), bigint is recommended. If the time field is static and only needs storage, timestamp saves space with 4 bytes but is limited to dates before 2038.