<blockquote>
<p>Surely "with timezone" has to store the timezone? The actual time might be normalised to UTC but it has to store the original timezone as well so it can return it. That's the whole point right?</p>
</blockquote>
<p>No, that's not how it works! :-) There's no offset stored, the information about the original timezone is ignored. If you store somethings as ".....10:05Z+0800" into a <code>timestamp with timezone</code> then it just gets stored as "....18:05UTC". Like I say, I think the column type names are a bit misleading.</p>
<p><a href="https://stackoverflow.com/a/9576170/105451" rel="nofollow">https://stackoverflow.com/a/9576170/105451</a> is another good article. It notes "The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored - or and output modifier used to compute the local time to display - with appended time zone offset."</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-440627280">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLb2SSaGbQ65qwCj_zwBVeb8QxJ8tks5uxTV3gaJpZM4A1Xpw">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLVmd7LDtOmKMUizKjke4pThrWBUFks5uxTV3gaJpZM4A1Xpw.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 Surely \"with timezone\" has to store the timezone? The actual time might be normalised to UTC but it has to store the original timezone as well so it can return it. That's the whole point right?\r\n\r\nNo, that's not how it works! :-) There's no offset stored, the information about the original timezone is ignored. If you store somethings as \".....10:05Z+0800\" into a `timestamp with timezone` then it just gets stored as \"....18:05UTC\". Like I say, I think the column type names are a bit misleading.\r\n\r\nhttps://stackoverflow.com/a/9576170/105451 is another good article. It notes \"The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored - or and output modifier used to compute the local time to display - with appended time zone offset.\""}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440627280"}}}</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-440627280",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440627280",
"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-440627280"
}
],
"@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>