[Tile-serving] [openstreetmap/osm2pgsql] New middle tables experiments (Issue #1970)

[Paul Norman]

> or with timestamps we get from outside the database (most importantly OSM files which always use UTC or the osm.org web site which shows also in UTC (if you mouse over the "33 days ago" message))

Because we're using UTC timestamps is the reason to use timestamptz! Specifically [don't use timestamp (without time zone) to store UTC times](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times).

> I agree with "the PostgreSQL experts" that theirs is the right choice for most cases and that if you don't want to think about what the best solution for your use case is than you should do as they say. But every use case is different

I've considered this use case, and there's nothing unusual about it. Storing UTC time is a common use-case that is well documented.

Some practical problems
- Say I want to find how old an object is as an `interval`. If I get the date of the newest node from osm.org and compare it to now(), I do `SELECT now() - '2023-05-28T07:36:45Z'::timestamp;` and get a negative interval. With `timestamptz` I get the right answer.
- My server with osm2pgsql runs in a different time zone than my desktop. I'll get different results depending on where I query from

Your reason for wanting `timestamp` is 4 of the last list on https://community.spiceworks.com/topic/2454825-zone-of-misunderstanding, linked from the PostgreSQL wiki.

If you want to display timestamps in UTC in some DBs and in local time in others, set the timezone specifically for that database. This is breaking behavior for others to force your preferences on them.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1970#issuecomment-1565975306
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/1970/1565975306 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230528/5099b17c/attachment.htm>