英文:
Difference UnixNano and MySQL usage
问题
我正在为我的新Go系统构建一个符合ISO和RFC标准的核心。我正在使用MySQL，并且目前正在确定最重要的基本表的最佳设置。
我正在努力弄清楚如何在数据库中存储日期时间。我希望在数据库中保存的时间占用空间和查询能力之间取得良好的平衡，同时与UTC的兼容性和易于时区转换，以便在Go/MySQL中插入和检索数据时不会出现烦人的冲突。
我知道在我的问题标题背景下，这听起来有点奇怪。但是我看到很多包装器、ORM等仍然存储UNIX时间戳（微秒？）。我认为最好的做法是始终存储UTC纳秒时间戳，并且愿意放弃日期/时间查询功能。我不想在运行系统时遇到大量不同的国家/语言/时区/货币/翻译等问题（国际化和本地化）。我之前在工作中遇到过这些问题，让我抓狂到最后不得不在整个代码库中应用大量修复措施，以至少对一些转换进行修复。我不希望这种情况发生在我的系统中。如果这意味着我必须始终进行一些额外的编码以保持所有存储的时间在正确的UTC+0时区，我会接受这一点。根据ISO-8601和时区偏差和夏令时，我将确定日期/时间的输出。
上述故事是基于观点的。但是我的实际问题是，选择Go的时间戳作为存储的INT与MySQL的TIMESTAMP或DATETIME哪个更有效？
1）从存储的角度来看，哪个最优？
2）从时区约定的角度来看，哪个最优？
3）从速度和MySQL查询的角度来看，哪个最优？
英文:
I am building a ISO and RFC complaint core for my new Go system. I am using MySQL and am currently figuring out the most optimal setup for the most important base-tables.
I am trying to figure out how to store the date-time in the database. I want to aim at a good balance between the space the saved time in the database will occupy, but also the query-capabilties and the compatibility with UTC and easy timezone conversion that doesn't give annoying conflicts for inserting and retrieving data into/from Go/MySQL.
I know this sounds a bit weird in context to the title of my question. But I see a lot of wrappers, ORM's and such still storing UNIX timestamps (microseconds?). I think it would be good to just always store UTC nano timestamps and just accepting losing the date/time querying functionalities. I don't want to get into problems when running the system with tons of different countries/languages/timezones/currencies/translations/etc. (internationalizations and localizations). I already encountered these problems before with some systems at work and it drove me nuts to the point where eventually tons of fixes had to be applied through the whole codebase to at least some of the conversion back into order. I don't want this to happen in my system. If it means I always have to do some extra coding to keep all stored times in correct UTC+0, I will take that for granted. Based on ISO-8601 and the timezone aberrations and daytime-savings I will determine the output of the date/time.
The story above is opinion based. But my actual question would be what solely is more efficient to choose Go's timestamp as INT stored vs MySQL TIMESTAMP or DATETIME;
1.) What is most optimal considering storage?
2.) What is most optimal considering timezone conventions?
3.) What is most optimal considering speed and MySQL querying?
答案1
得分: 1
所有这些问题的答案很简单，只需使用t.UTC().UnixNano()将时间戳以UTC时间存储，要记住时间是int64类型，因此无论精度如何，它在数据库中始终占据8个字节的空间。
英文:
The answer to all these questions is simply storing the timestamp in UTC time with t.UTC().UnixNano(), keep in mind that time is int64 so it will always be 8 bytes in the database regardless of precision.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论