通过mySQL或php获得时间戳更好吗？

I'm starting to design a database and before too many records get inputted, I want to think into the future and collect as much data as possible. I think it would be good for me to know when the record was added. Based on your experience, is it better to do this through mySQL via datetime or through php via the date function. I'll be using php to input all the values, so it would simply be another field.

我开始设计一个数据库,在输入太多记录之前,我想考虑未来并收集尽可能多的数据。我想知道什么时候添加记录对我有好处。根据您的经验,通过日期时间通过mySQL或通过日期函数通过php完成此操作会更好。我将使用php输入所有值,因此它只是另一个字段。

So far, I like the php approach because I can customize it to take up minimal space: yymmddhhmm & time zone.

到目前为止,我喜欢php方法,因为我可以自定义它以占用最小的空间:yymmddhhmm&时区。

Based on your experience, what is the best way to store this data or are the two ways indifferent?

根据您的经验,存储这些数据的最佳方式是什么,或者两种方式无关紧要?

Also, what time zone would you suggest using? The time zone where I am located or GMT? Is it best to use GMT if say I were to move later on or if individuals from multiple timezones administered the database.

另外,您建议使用什么时区?我所在的时区或GMT?是否最好使用GMT,如果我以后要移动,或者来自多个时区的人员管理数据库。

2 个解决方案

#1

Store it as DATETIME/TIMESTAMP in MySQL, it is stored as an integer anyway, just goes in and comes out as a timestamp. Store the data in UTC.

在MySQL中将它存储为DATETIME / TIMESTAMP,无论如何它都存储为整数,只是进入并作为时间戳出来。以UTC格式存储数据。

You can manipulate the timestamp in PHP by constructing it with DateTime() and then going from there.

您可以通过使用DateTime()构建它来操纵PHP中的时间戳,然后从那里开始。

This also allows you to put a NOT NULL DEFAULT CURRENT_TIMESTAMP on the column, which saves you actively having to build it in php.

这也允许你在列上放置一个NOT NULL DEFAULT CURRENT_TIMESTAMP,这可以节省你在php中构建它的积极性。

#2

For simplicity, I suggest using MySQL's timestamp field. Because the database then understands what it is, it's stored much more efficiently than your text version (as a number, rather than a string of characters), and you can do more with it.

为简单起见,我建议使用MySQL的时间戳字段。因为数据库然后理解它是什么,它的存储效率比文本版本(作为一个数字,而不是一串字符)更高效,你可以用它做更多的事情。

For example:

mysql> CREATE TABLE foo (something TEXT NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO foo (something) VALUES ("one");
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO foo (something) VALUES ("two");
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM foo;
+-----------+---------------------+
| something | created             |
+-----------+---------------------+
| one       | 2013-09-18 22:57:01 |
| two       | 2013-09-18 22:57:03 |
+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT something, NOW() - created as seconds_since_insert FROM foo;
+-----------+----------------------+
| something | seconds_since_insert |
+-----------+----------------------+
| one       |                  136 |
| two       |                  134 |
+-----------+----------------------+
2 rows in set (0.00 sec)

#1