Performance Comparison of datetime, timestamp, and bigint for Storing Time in MySQL

When storing time in a MySQL database, three common column types are datetime , timestamp , and bigint . This article investigates which type is most suitable for different use cases.

Data Preparation

A table users is created with three time columns:

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 corresponding Java entity class defines matching fields:

public class Users {
    private Long id;
    private Date timeDate;          // datetime
    private Timestamp timeTimestamp; // timestamp
    private long timeLong;           // bigint
}

A MyBatis mapper interface provides an INSERT method:

@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);
}

A test class inserts 500,000 rows, each with the current system time converted to the three column types.

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 Speed Test (Range Queries)

datetime range query took 0.171 s

timestamp range query took 0.351 s

bigint range query took 0.130 s

Conclusion: In InnoDB, range queries perform best with bigint , followed by datetime , then timestamp .

SQL Group By Speed Test

Group by datetime took 0.176 s

Group by timestamp took 0.173 s

Conclusion: Grouping by timestamp is slightly faster than datetime , but the difference is minimal.

SQL Order By Speed Test

Order by datetime took 1.038 s

Order by timestamp took 0.933 s

Order by bigint took 0.775 s

Conclusion: Sorting performance ranks as bigint > timestamp > datetime under InnoDB.

Summary

If time fields need frequent operations such as range queries or sorting, bigint is recommended. If the time field is only stored without manipulation, timestamp saves space (4 bytes) but is limited to dates before 2038.