In MySQL database, datetime, bigint, and timestamp are used to represent time selection. Which one is the most efficient for storing time?

In the database, datetime, bigint, and timestamp can be used to represent time. So which type is more appropriate to choose to store time?

# Post-data preparation

Insert 50w data into the database through the program

Data Sheet:

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

Among them, time_long, time_timestamp, and time_date are different storage formats for the same time.

Entity class users

/**
 * @author hetiantian
 * @date 2018/10/21
 * */
@Builder
@Data
public class Users {
    /**
     * Self-incrementing unique id
     * */
    private Long id;

    /**
     * Date type time * */
    private Date timeDate;

    /**
     *Time of timestamp type* */
    private Timestamp timeTimestamp;

    /**
     * Long type time * */
    private long timeLong;
}

DAO layer interface

/**
 * @author hetiantian
 * @date 2018/10/21
 * */
@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);
}

The test class inserts data into the database

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

To generate data code, go to github: https://github.com/TiantianUpup/sql-test/ If you don’t want to use code generation, but want to import data through a sql file, the sql file network disk address is attached at the end of the article.

# SQL query rate test

Query by datetime type:

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

Time taken: 0.171

Query by timestamp type

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

Time taken: 0.351

Query by bigint type

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

Time taken: 0.130s

Conclusion Under the InnoDB storage engine, the performance of searching by time range is bigint > datetime > timestamp

# SQL group rate test

Using bigint for grouping will group each piece of data. If bigint is converted before grouping, there is no point in comparing. Conversion also takes time.

Group by datetime type:

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

Time taken: 0.176s

Group by timestamp type:

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

Time taken: 0.173s

Conclusion: Under the InnoDB storage engine, by time grouping, the performance of timestamp > datetime, but the difference is not big.

# sql sorting rate test

Sort by datetime type:

select * from users order by time_date

Time taken: 1.038s

Sort by timestamp type

select * from users order by time_timestamp

Time taken: 0.933s

Sort by bigint type

select * from users order by time_long

Time taken: 0.775s

Conclusion: Under the InnoDB storage engine, by time sorting, the performance of bigint > timestamp > datetime

# Summary

If you need to operate on the time field (such as searching or sorting by time range, etc.), it is recommended to use bigint. If the time field does not need any operation, it is recommended to use timestamp. Using 4 bytes to save space is more convenient, but the recorded time is limited to 2038.

The SQL file network disk address in the article: Link: https://pan.baidu.com/s/1cCRCxtTlPriXMERGsbnb_A Extraction code: hbq2

This concludes this article about choosing between datetime, bigint, and timestamp to represent time in MySQL database, and which one is the most efficient for storing time. For more information about database datetime, bigint, and timestamp, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!