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!

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

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.

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

I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

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.

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

I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

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.

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

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

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

edited Nov 21 '18 at 22:29

Laurenz Albe

47.5k102748

asked Nov 21 '18 at 20:42

jdixon04

468514

asked Nov 21 '18 at 20:42

jdixon04

468514

asked Nov 21 '18 at 20:42

jdixon04

468514

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.

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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.

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

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.

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

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.

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

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.

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

edited Nov 21 '18 at 22:32

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

answered Nov 21 '18 at 22:27

Laurenz Albe

47.5k102748

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

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 |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu