<blockquote>
<p>it works out of the box.</p>
</blockquote>
<p>Not quite - it <em>appears</em> to work out of the box. This is because the clients you list all assume that the data stored in the 'timestamp' columns is UTC, when it is actually 'local time'. Since the assumption is symmetric (i.e. the same mistake on writing and reading), you don't see the problem. That, however, is not sufficient to state that the problem doesn't exist. :-)</p>
<blockquote>
<p><a class="user-mention" data-hovercard-type="user" data-hovercard-url="/hovercards?user_id=1190866" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/pnorman">@pnorman</a>, <a class="user-mention" data-hovercard-type="user" data-hovercard-url="/hovercards?user_id=360803" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/gravitystorm">@gravitystorm</a> : what would be your take on how to address the breakage of cgimap, osmosis, and other tools due to this change on a non-UTC system?</p>
</blockquote>
<p>Sure, we need to fix any tools that already make broken assumptions. I'm not going to suggest we make changes that break anything, this whole topic is about fixing things properly. But this change will make the timestamp storage correct, and allow people to make future clients (or even trivial things, like calling now() from an SQL statement) work reliably without having to deal with this headache over and over for ever more.</p>
<blockquote>
<p>If the columns need to be rewritten then I think this will pretty much be a non-starter as we would need a downtime measured in hours if not days.</p>
</blockquote>
<p>We can't just say that we'll never change anything in our tables ever again! If this does involve large table changes, then we can work out what the zero-downtime migration path would be, and do that. We could practise multi-stage migrations on the smaller tables where there is less impact.</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLZuH8Nzty-UfvAqt89-xUq6LeDCPks5uy_yxgaJpZM4A1Xpw">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLWCmDHWoAipulRY-7-Ri7_3Pj7iDks5uy_yxgaJpZM4A1Xpw.gif" height="1" width="1" alt="" /></p>
<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/openstreetmap/openstreetmap-website","title":"openstreetmap/openstreetmap-website","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/openstreetmap/openstreetmap-website"}},"updates":{"snippets":[{"icon":"PERSON","message":"@gravitystorm in #375: \u003e  it works out of the box.\r\n\r\nNot quite - it *appears* to work out of the box. This is because the clients you list all assume that the data stored in the 'timestamp' columns is UTC, when it is actually 'local time'. Since the assumption is symmetric (i.e. the same mistake on writing and reading), you don't see the problem. That, however, is not sufficient to state that the problem doesn't exist. :-)\r\n\r\n\u003e @pnorman, @gravitystorm : what would be your take on how to address the breakage of cgimap, osmosis, and other tools due to this change on a non-UTC system?\r\n\r\nSure, we need to fix any tools that already make broken assumptions. I'm not going to suggest we make changes that break anything, this whole topic is about fixing things properly. But this change will make the timestamp storage correct, and allow people to make future clients (or even trivial things, like calling now() from an SQL statement) work reliably without having to deal with this headache over and over for ever more.\r\n\r\n\u003e If the columns need to be rewritten then I think this will pretty much be a non-starter as we would need a downtime measured in hours if not days.\r\n\r\nWe can't just say that we'll never change anything in our tables ever again! If this does involve large table changes, then we can work out what the zero-downtime migration path would be, and do that. We could practise multi-stage migrations on the smaller tables where there is less impact."}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
},
{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"hideOriginalBody": "false",
"originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB",
"title": "Re: [openstreetmap/openstreetmap-website] Switch to TIMESTAMP WITH TIME ZONE for current_* tables (#375)",
"sections": [
{
"text": "",
"activityTitle": "**Andy Allan**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@gravitystorm",
"facts": [

]
}
],
"potentialAction": [
{
"name": "Add a comment",
"@type": "ActionCard",
"inputs": [
{
"isMultiLine": true,
"@type": "TextInput",
"id": "IssueComment",
"isRequired": false
}
],
"actions": [
{
"name": "Comment",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 375,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}"
}
]
},
{
"name": "Close issue",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 375\n}"
},
{
"targets": [
{
"os": "default",
"uri": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239"
}
],
"@type": "OpenUri",
"name": "View on GitHub"
},
{
"name": "Unsubscribe",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 13990512\n}"
}
],
"themeColor": "26292E"
}
]</script>