<blockquote>
<p>The "Z" (zulu) character in the timestamp string implies that all timestamps should be returned in UTC-0, no matter what time zone the server runs in.</p>
</blockquote>
<p>That's not quite what it's doing. It's taking the timestamp from the database, formatting it appropriately and then sticking an actual letter Z at the end of the string (like the T, it's double quoted - it's not a formatting character like YYYY so postgres is just adding that character to the string). So there's no timezone conversion happening in the to_char method. It's taking the <code>timestamp without timezone</code> and making it look like a UTC timestamp. Which of course works fine if the server is in UTC but leads to other problems in other situations.</p>
<blockquote>
<p>This ended up creating wrong timestamps all over the place.</p>
</blockquote>
<p>Yes, this is the same problem. Osmosis will have read the UTC timestamp from the XML file, handled it internally as UTC all through the pipelines, and then when writing it to the database the pg connection library will have converted that time to local time when storing it as <code>timestamp without timezone</code>. Then cgimap / rails port / whatever will have used that timestamp, assumed it was UTC and there's the problem.</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-440690343">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLX7fFG13ak31fTYuUAmwWuZLC_hIks5uxWh_gaJpZM4A1Xpw">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLZjtFtTs8jrO-sg0eamEQZZ7s3dXks5uxWh_gaJpZM4A1Xpw.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 The \"Z\" (zulu) character in the timestamp string implies that all timestamps should be returned in UTC-0, no matter what time zone the server runs in.\r\n\r\nThat's not quite what it's doing. It's taking the timestamp from the database, formatting it appropriately and then sticking an actual letter Z at the end of the string (like the T, it's double quoted - it's not a formatting character like YYYY so postgres is just adding that character to the string). So there's no timezone conversion happening in the to_char method. It's taking the `timestamp without timezone` and making it look like a UTC timestamp. Which of course works fine if the server is in UTC but leads to other problems in other situations.\r\n\r\n\u003e This ended up creating wrong timestamps all over the place.\r\n\r\nYes, this is the same problem. Osmosis will have read the UTC timestamp from the XML file, handled it internally as UTC all through the pipelines, and then when writing it to the database the pg connection library will have converted that time to local time when storing it as `timestamp without timezone`. Then cgimap / rails port / whatever will have used that timestamp, assumed it was UTC and there's the problem."}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440690343"}}}</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-440690343",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440690343",
"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-440690343"
}
],
"@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>