Why use long to replace date type in DB design?

This is the back-end small class of the monastery. Each article is shared from

[background introduction] [knowledge analysis] [common problems] [solutions] [coding practice] [extended thinking] [more discussion] [References]

Eight aspects of in-depth analysis of back-end knowledge / skills. This article shares:

[why is long used to replace date type in DB design?]

[small java class of Xiuzhen academy] why should long be used to replace date type in DB design?

Hello, I'm a student of the 12th session of Shenzhen Branch of it Academy. I'm an honest, pure and kind java programmer. Today, I'd like to share with you the in-depth thinking of Java task 1 on the official website of the Academy - why should long be used to replace date type in the design of DB?

(1) Background introduction

When operating the records in the database, the processing of time data needs to be involved, such as create_ at,update_ At and other fields. The database has a special type for storing time, date, and you can also set the type to long. What is the difference between the two?

(2) Knowledge analysis

Type representing time in MySQL

Date: date, occupying three bytes, 1000-01-01-9999-12-31

Datetime: date + time, from "1000-01-01 00:00:00" to "9999-12-31 23:59:59", which takes 8 bytes. The datetime type is suitable for recording the original creation time of data, because no matter how you change the values of other fields in the record, the values of the datetime field will not change unless you change it manually.

Timestamp: date + time, which takes up 4 bytes, "197001080001 -- 20380119111407. The timestamp type is suitable for recording the last modification time of data, because as long as you change the values of other fields in the record, the values of the timestamp field will be updated automatically.

Bigint type in MySQL

Bignit represents an integer from - 2 ^ 63 to 2 ^ 63-1 (i.e. from - 9223372036854785808 to 9807), which occupies eight bytes of storage space.

(3) Frequently asked questions

Clearly, the date type should represent the time. Why use bigint instead?

In fact, online opinions on this issue are not unified

Some people's Views:

What type of storage should be used

Here is: since you want to store time, why not use the type of time?

(4) Solution

1. Because date has a fixed format, different regions have different time representation methods, and foreign countries have summer time and winter time, which is very troublesome

2. In fact, bigint can also clearly represent time

3. Most of the time, we don't care about a certain time point, but the time required after an action occurs. Bigint is very convenient for subtraction without conversion

(5) Coding practice

(6) Expand thinking

(7) References

[reference 1]

(8) More discussion

Q1: what are the common array based data structures?

A1: ArrayList is the most common array based in Java. Generally, if query operations are frequent, elements are repeatable, and do not need to be stored in the form of key value, ArrayList will be used to store data.

Q2: when is it suitable to use arrays

A2: when the index is meaningful, such as a student in a class, the student number corresponds to a student (of course, the student number cannot be too large, such as 1, 2, 3...) At this time, array is more suitable.

Q3: in addition to the linear data structure of array, what other linear data structures are there?

A3: in addition, there are linked lists, stacks and queues.

(9) Acknowledge

(10) Conclusion

That's all for today's sharing. Welcome to like, leave messages, forward and throw bricks~

Ppt link video link