您的位置: 首页  >  技术问答  >  PHP + MySQL存储UTC时间戳与日期时间

PHP + MySQL存储UTC时间戳与日期时间

分类: 技术问答 • 2022-03-11 13:26:37
问题描述：

I'm struggling to make a decision between timestamp and datetime. I did fint similar questions, but none gave me a definite answer.

The scenario is as follows:

  1. User creates an event in their calendar (using their timezone)
  2. Event is persised with date and time (in some "neutral" timezone)
  3. Another user can view this event (using their timezone)

I have to account for DST and all date & time related things. I believe dates should be stored in UTC timezone being neutral.

So my question is whether someone could provide me a workflow they utilized in this situation?

I was thinking that in PHP I would use DateTime obviously and:

  1. User creates an event => I take DateTime in his timezone -> convert it to timestamp (getTimestamp) and persist that way
  2. When some user wants to view that event => I fetch the timestamp from database -> create new DateTime object with his timezone -> set the timestamp (setTimestamp) and this way have that time displayed in his timezone

Is there any drawback of my workflow?

I will be using Symfony 2, so any Symfony 2 workflow would be best, but other than that I will take anything!

我很难在时间戳和日期时间之间做出决定。 我确实提出了类似的问题，但没有一个给我一个明确的答案。 p>

场景如下： p>

  1. 用户创建了一个 日历中的事件（使用他们的时区） li>
  2. 事件按日期和时间显示（在某些“中性”时区） li>
  3. 另一个用户可以查看此事件（使用 他们的时区） li> ol>

    我必须考虑夏令时和所有日期＆amp; 时间相关的事情。 我相信日期应该以UTC时区存储为中性。 p>

    所以我的问题是，是否有人可以为我提供他们在这种情况下使用的工作流程？ p>

    我当时认为在PHP中我会明显使用DateTime并且： p>

    1. 用户创建一个事件=＆gt; 我在他的时区中使用DateTime - ＆gt; 将其转换为时间戳（getTimestamp）并以此方式保持 li>
    2. 当某些用户想要查看该事件=＆gt; 我从数据库中获取时间戳 - ＆gt; 用他的时区创建新的DateTime对象 - ＆gt; 设置时间戳（setTimestamp），这种时间显示在他的时区 li> ol>

      我的工作流程有任何缺点吗？ p> div>

答

Your workflow is fine.

Whenever you need to fulfil multi timezone requirement, it is better to store time in database as utc timestamp or unix epoch. unix epoch is well supported and i would suggest this. Even from the client side you can send unix epoch to the server without worrying about timezone. If you use utc timestamp you will need to convert between timezones. with unix epoch it is still conversion but it is very straight forward and well supported in most of the date object constructs.

And when you need to retrieve and display time, you will construct the date object using unix epoch and the time zone for that user.

相关推荐