Postgres “time zone at” isn't respecting mountain standard time when converting
I have a column starts_at with a type of TIMESTAMP WITHOUT TIME ZONE because it's representing the time of an appointment and should not change during a DST shift.
However, our library that handles recurring appointments needs this time in UTC. I am attempting to convert starts_at to UTC, but am seeing that I'm getting times representing MDT (daylight savings time) rather than MST (standard time).
For example, take the following:
SELECT starts_at, timezone('America/Denver', starts_at) AS new_starts_at
I would expect to get the following result:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 20:05:00+00
Instead, I'm getting the following:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 19:05:00+00
new_starts_at should be returning in MST, which would be 2018-09-04 20:05:00+00. My impression was that using the Olsen timezone (America/Denver) would inform Postgres of whether or not there was a DST shift in place. If I replace America/Denver with MST, I see the correct result.
I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!
postgresql timestamp-with-timezone
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
add a comment |
I have a column starts_at with a type of TIMESTAMP WITHOUT TIME ZONE because it's representing the time of an appointment and should not change during a DST shift.
However, our library that handles recurring appointments needs this time in UTC. I am attempting to convert starts_at to UTC, but am seeing that I'm getting times representing MDT (daylight savings time) rather than MST (standard time).
For example, take the following:
SELECT starts_at, timezone('America/Denver', starts_at) AS new_starts_at
I would expect to get the following result:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 20:05:00+00
Instead, I'm getting the following:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 19:05:00+00
new_starts_at should be returning in MST, which would be 2018-09-04 20:05:00+00. My impression was that using the Olsen timezone (America/Denver) would inform Postgres of whether or not there was a DST shift in place. If I replace America/Denver with MST, I see the correct result.
I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!
postgresql timestamp-with-timezone
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
add a comment |
I have a column starts_at with a type of TIMESTAMP WITHOUT TIME ZONE because it's representing the time of an appointment and should not change during a DST shift.
However, our library that handles recurring appointments needs this time in UTC. I am attempting to convert starts_at to UTC, but am seeing that I'm getting times representing MDT (daylight savings time) rather than MST (standard time).
For example, take the following:
SELECT starts_at, timezone('America/Denver', starts_at) AS new_starts_at
I would expect to get the following result:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 20:05:00+00
Instead, I'm getting the following:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 19:05:00+00
new_starts_at should be returning in MST, which would be 2018-09-04 20:05:00+00. My impression was that using the Olsen timezone (America/Denver) would inform Postgres of whether or not there was a DST shift in place. If I replace America/Denver with MST, I see the correct result.
I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!
postgresql timestamp-with-timezone
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
I have a column starts_at with a type of TIMESTAMP WITHOUT TIME ZONE because it's representing the time of an appointment and should not change during a DST shift.
However, our library that handles recurring appointments needs this time in UTC. I am attempting to convert starts_at to UTC, but am seeing that I'm getting times representing MDT (daylight savings time) rather than MST (standard time).
For example, take the following:
SELECT starts_at, timezone('America/Denver', starts_at) AS new_starts_at
I would expect to get the following result:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 20:05:00+00
Instead, I'm getting the following:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 19:05:00+00
new_starts_at should be returning in MST, which would be 2018-09-04 20:05:00+00. My impression was that using the Olsen timezone (America/Denver) would inform Postgres of whether or not there was a DST shift in place. If I replace America/Denver with MST, I see the correct result.
I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!
postgresql timestamp-with-timezone
postgresql timestamp-with-timezone
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
edited Nov 21 '18 at 22:29
Laurenz Albe
45.1k102747
45.1k102747
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
asked Nov 21 '18 at 20:42
jdixon04jdixon04
468513
468513
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420180%2fpostgres-time-zone-at-isnt-respecting-mountain-standard-time-when-converting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
add a comment |
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
add a comment |
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
edited Nov 21 '18 at 22:32
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
answered Nov 21 '18 at 22:27
Laurenz AlbeLaurenz Albe
45.1k102747
45.1k102747
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
add a comment |
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
13:05 Denver is 20:05 in UTC. During daylight savings time, it's 19:05
– jdixon04
Nov 21 '18 at 22:32
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Ahh, nevermind. I see the issue. Thank you!
– jdixon04
Nov 21 '18 at 22:33
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
Right, and in September, daylight savings time was still in effect.
– Laurenz Albe
Nov 21 '18 at 22:34
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420180%2fpostgres-time-zone-at-isnt-respecting-mountain-standard-time-when-converting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
