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

Question

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!

Answer 1

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.