<p>I tried <code>alter table current_nodes alter column timestamp type timestamptz;</code> on a system running on UTC+1 (Eu/Berlin). Timestamps used to be correct before, and are now off by 1 or 2 hours, depending on summer/winter time. So running this statement on a non UTC+0 system seems like a bad idea.<br>
I'm pretty sure there are some alternatives available to get this right?</p>
<pre><code>
     id     |  latitude  | longitude  | changeset_id | visible |         timestamp          |    tile    | version 
------------+------------+------------+--------------+---------+----------------------------+------------+---------
 5002730175 |  423712670 | -834339064 |         1061 | t       | 2018-06-08 20:38:42.691478 | 1701877902 |       1


http://localhost:3000/api/0.6/node/5002730175

node id="5002730175" changeset="1061" timestamp="2018-06-08T20:38:42Z

Change timestamp file column type:

alter table current_nodes alter column timestamp type timestamptz;


     id     |  latitude  | longitude  | changeset_id | visible |           timestamp           |    tile    | version 
------------+------------+------------+--------------+---------+-------------------------------+------------+---------
 5002730175 |  423712670 | -834339064 |         1061 | t       | 2018-06-08 20:38:42.691478+02 | 1701877902 |       1

http://localhost:3000/api/0.6/node/5002730175

node id="5002730175" changeset="1061" timestamp="2018-06-08T18:38:42Z"
</code></pre>

<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-440760686">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLa6uKnGnra8tVQ7HgBCZlGmZCz_Xks5uxZZWgaJpZM4A1Xpw">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLb8tLi9G0u4IHNdvFpe5nYyhKzBDks5uxZZWgaJpZM4A1Xpw.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":"@mmd-osm in #375: I tried `alter table current_nodes alter column timestamp type timestamptz;` on a system running on UTC+1 (Eu/Berlin). Timestamps used to be correct before, and are now off by 1 or 2 hours, depending on summer/winter time. So running this statement on a non UTC+0 system seems like a bad idea.\r\nI'm pretty sure there are some alternatives available to get this right?\r\n\r\n```\r\n\r\n     id     |  latitude  | longitude  | changeset_id | visible |         timestamp          |    tile    | version \r\n------------+------------+------------+--------------+---------+----------------------------+------------+---------\r\n 5002730175 |  423712670 | -834339064 |         1061 | t       | 2018-06-08 20:38:42.691478 | 1701877902 |       1\r\n\r\n\r\nhttp://localhost:3000/api/0.6/node/5002730175\r\n\r\nnode id=\"5002730175\" changeset=\"1061\" timestamp=\"2018-06-08T20:38:42Z\r\n\r\nChange timestamp file column type:\r\n\r\nalter table current_nodes alter column timestamp type timestamptz;\r\n\r\n\r\n     id     |  latitude  | longitude  | changeset_id | visible |           timestamp           |    tile    | version \r\n------------+------------+------------+--------------+---------+-------------------------------+------------+---------\r\n 5002730175 |  423712670 | -834339064 |         1061 | t       | 2018-06-08 20:38:42.691478+02 | 1701877902 |       1\r\n\r\nhttp://localhost:3000/api/0.6/node/5002730175\r\n\r\nnode id=\"5002730175\" changeset=\"1061\" timestamp=\"2018-06-08T18:38:42Z\"\r\n```"}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440760686"}}}</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-440760686",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440760686",
"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": "**mmd**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@mmd-osm",
"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-440760686"
}
],
"@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>