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

[Jochen Topf]

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. Basically it comes down to what the "operations" are that we want to do with the data. In our case I believe the most often used "operation" is to look at the data and compare it with timestamps in the same database (in which case type doesn't matter) 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)). Having the data show up in my local time zone which is the default when opening a pgsql session is rather annoying, especially with daylight saving shifts involved. And I *never* care about my local time when an edit was made. I might care about the local time zone of the editor, but that information is lost anyway. What I care about is comparing timestamps to see what comes before what. The only time I might care about comparing with local time is to get something like that "edited 3 hours ago" thing, not something that will happen a lot in an osm2pgsql database, but I agree that this use case becomes a little bit more complex.

Depending on what you do if you write a program to do something with the data you might have to do a little bit more work to set the time zone, but that's okay, you are writing program anyway. The crucial part is when you do ad-hoc queries, and they are easier and more natural when storing the data without time zone. And yes, I know that you can change the time zone in your settings so that everything shows up in UTC anyway, but then I either have to remember to do this for every session or I have to put that into a config file which breaks every other case where I might have a database that contains timestamps that I *do* want to see in my local time. Unfortunately there is no way to say, this is a timestamp with UTC time and always show it to me in UTC time.

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

Message ID: <openstreetmap/osm2pgsql/issues/1970/1565941858 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230527/6127db10/attachment-0001.htm>