I have two choices of storing date and time in my database.
我可以在数据库中存储日期和时间。
-
Generate the time & date from time function in php and then storing in database into int datatype which is of 4 bytes.
在php中生成时间和日期函数，然后在数据库中存储为4字节的int数据类型。
-
Generate the time & date during insertion in database into datetime datatype which is of 8 bytes.
在将数据库插入到datetime数据类型(8字节)时生成时间和日期。
My question is which type will make my SQL queries faster if I use date&time column for sorting.
我的问题是，如果我使用date&time列进行排序，哪种类型的SQL查询会更快。
3 个解决方案
#1
2
I always hate it building queries on a DB that contains human unreadable date and time values in int format.
我总是讨厌在DB上构建查询，其中包含人工不可读的日期和时间值(int格式)。
Maybe the query will be a nano second faster if you use int but is it really worth it? I say no!
如果使用int类型，查询速度可能会提高一纳秒，但这真的值得吗?我说不!
#2
2
Use a TIMESTAMP datatype. It's stored as a number, but returned formatted. So it's faster for sorting, and more human-readable.
使用时间戳数据类型。它存储为一个数字，但是返回的格式是格式化的。因此，它可以更快地进行排序，而且更易于阅读。
#3
0
You are better off using the native format of the database to store date times.
最好使用数据库的本机格式来存储日期时间。
I can see almost no occasion where you would want to use another binary format for this purposes. That would make that component of the database essentially inoperable for other access methods.
我几乎看不到你想要使用另一种二进制格式来达到这个目的。这将使数据库的这个组件基本上不能用于其他访问方法。
As for human readability, you can solve that issue by having views access the tables (in other databases you can define a table with computed columns) that provide the dates in human readable format. Also, tools that know the database should produce the output in human readable format.
至于人类可读性，您可以通过让视图访问以人类可读格式提供日期的表(在其他数据库中，您可以定义具有计算列的表)来解决这个问题。此外，了解数据库的工具应该以人类可读的格式生成输出。
Unless you have a very large database or are in a very tightly constrained environment, then don't worry about storage. You are probably not thinking about the extra bits that are stored when you allow NULLs for a column, or the extra padding that might go between fields when they are no aligned on hardware word boundaries, or the empty space on data pages because the records don't align on page boundaries.
除非您有一个非常大的数据库或处于非常严格的约束环境中，否则不要担心存储问题。你可能不考虑额外的比特存储时允许null列,或额外的填充时,字段之间可能会没有对齐硬件单词边界,或者在数据页的空白页因为记录不一致边界。
If space is such an important consideration, then you might want to develop your own date/time format to see if you can get it down to 2 or 3 bytes.
如果空间是如此重要的考虑因素，那么您可能需要开发自己的日期/时间格式，看看是否可以将其减少到2或3个字节。
#1
2
I always hate it building queries on a DB that contains human unreadable date and time values in int format.
我总是讨厌在DB上构建查询，其中包含人工不可读的日期和时间值(int格式)。
Maybe the query will be a nano second faster if you use int but is it really worth it? I say no!
如果使用int类型，查询速度可能会提高一纳秒，但这真的值得吗?我说不!
#2
2
Use a TIMESTAMP datatype. It's stored as a number, but returned formatted. So it's faster for sorting, and more human-readable.
使用时间戳数据类型。它存储为一个数字，但是返回的格式是格式化的。因此，它可以更快地进行排序，而且更易于阅读。
#3
0
You are better off using the native format of the database to store date times.
最好使用数据库的本机格式来存储日期时间。
I can see almost no occasion where you would want to use another binary format for this purposes. That would make that component of the database essentially inoperable for other access methods.
我几乎看不到你想要使用另一种二进制格式来达到这个目的。这将使数据库的这个组件基本上不能用于其他访问方法。
As for human readability, you can solve that issue by having views access the tables (in other databases you can define a table with computed columns) that provide the dates in human readable format. Also, tools that know the database should produce the output in human readable format.
至于人类可读性，您可以通过让视图访问以人类可读格式提供日期的表(在其他数据库中，您可以定义具有计算列的表)来解决这个问题。此外，了解数据库的工具应该以人类可读的格式生成输出。
Unless you have a very large database or are in a very tightly constrained environment, then don't worry about storage. You are probably not thinking about the extra bits that are stored when you allow NULLs for a column, or the extra padding that might go between fields when they are no aligned on hardware word boundaries, or the empty space on data pages because the records don't align on page boundaries.
除非您有一个非常大的数据库或处于非常严格的约束环境中，否则不要担心存储问题。你可能不考虑额外的比特存储时允许null列,或额外的填充时,字段之间可能会没有对齐硬件单词边界,或者在数据页的空白页因为记录不一致边界。
If space is such an important consideration, then you might want to develop your own date/time format to see if you can get it down to 2 or 3 bytes.
如果空间是如此重要的考虑因素，那么您可能需要开发自己的日期/时间格式，看看是否可以将其减少到2或3个字节。