[Escalated to 2nd Tier] Time stamp custom field needs to support time zone as well

[Author]

Posts

[Clifford]

Thanks for the thoughtful reply, Nigel. I'm glad the issue is at least understood and given its due respect.

The recommendation from hidden link is:

To store information in a database, I would use a char column-type to store the whole America/Santiago time zone identifier and another char column-type to store the date/time (in the yyyy-mm-dd hh:ii:ss format).

(emphasis added)

There are examples at that link why storing a timestamp, even in UTC, is not reliable. Short answer: because of DST and each location can change its locale or DST rules.

An added benefit of storing in that recommended format is human readable database values.

This is also how The Events Calendar (the only WP calendar that handles time zones) stores the data: hidden link -- although it would be better if it allowed storing a different time zone for start time vs end time, as Google Calendar does.

To your idea of storing timestamps ensuring they're always set to UTC, I'm guessing by getting PHP's UTC offset and adjusting to be +00:00 offset, that's to my point... that's unreliable. Alternatively, if you were to use date_timezone_get() to convert the user input to UTC, why? That would be turning good data (datetime + zone) to bad (timestamp without zone).

With the time+zone data saved explicitly, I can ensure I'm choosing a specific datetime in a specific zone in the input UI (Types), and the output UI (Views) can allow me (e.g. "check this box to display time zone alongside datetime") to display the zone on the front end.

Bonus: when choosing the output format, it'd be sweet if MomentJS was easily supported to display the time and zone in the user's local (browser) time (just as an option or via a custom plugin), not needing to be the default.

Let me know if you've got follow-up discussion items.

[Luo Yang]

I think there is some misunderstandings:
1) Types plugin save custom date field value into database table "wp_postmeta", column "meta_value", that column is "longtext" type, it is WordPress built-in database table, we won't change the column type.

2) We have already confirm there is timezone problem in custom date field, it saves timestamp value in UTC timezone, as you mentioned above: it is unreliable.

3) But if we add timezone setting into custom date field, and save the value according to timezone settings, for example:
Date: 2018-09-14, timezone:Amsterdam, timestamp value: 1536876000

Then there will be other backward compatibility problem, since old version and the current version Types plugin, save the timestamp value as UTC timezone: 1536883200, other websites using Toolset plugins will be taken effect, so it won't be fixed easily.

Anyway, this thread has been escalated to our developers, as Nigel mentioned above: leave it to the devs to decide.

And I have marked this thread as "Escalated" status, will update here if there is any news.

[Clifford]

I was making the point of the data to be stored, not recommending to move away from postmeta table.

Looking forward to the eventual solution. Thanks for continuing to make Toolset better!

[Luo Yang]

I just checked it in our to-do list, did not get feedback from our developers yet, so nothing I can update here.

[ianG-5]

It its truly un believable that this is still an issue!

Just to run this point home again ( and I hope this is understood by 2nd tier!) as some in this thread seem to be getting it wrong. The issue isn't that a Unix timestamp is unreliable and needs to include time zone, Unix timestamp when implemented correctly is perfectly useable and predictable.
The issue is that toolset is using Unix timestamp without converting to UTC, which must be done! Unix timestamps are time since midnight of Jan 1 1970 in UTC