Display Date as Local Timzone, not UTC in AWS Quicksight
up vote
1
down vote
favorite
So reading through the AWS Quicksight docs, I have found the following information.
Handling Date Time Zones:
Amazon QuickSight uses UTC time for querying, filtering, and displaying date data.
When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.
I have a range of dates in my Athena dataset that I am analyzing in Quicksight. I would like to be able to view these dates in Quicksight as the local timezone representation, not as UTC format. Can anyone advise what would be the best approach for this would be or if it is possible at all? It seems if I utilize a calculated field function like formatDate(), or even custom SQL of 'AT TIME ZONE' then my date columns are converted to strings. Then any attempt to convert these strings back to a date simply convert the date back to UTC format.
I have tried converting the returned date string with:
parseDate({NEWDATE}, "yyyy-MM-dd HH:mm:ss.SSS ZZZ", "Australia/Melbourne")
However, this keeps raising an error "This function does not have the correct number of arguments".
Any advice is appreciated.
amazon-web-services amazon-quicksight
asked Nov 9 at 23:00
apollocr
185
add a comment |
up vote
1
down vote
favorite
So reading through the AWS Quicksight docs, I have found the following information.
Handling Date Time Zones:
Amazon QuickSight uses UTC time for querying, filtering, and displaying date data.
When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.
I have a range of dates in my Athena dataset that I am analyzing in Quicksight. I would like to be able to view these dates in Quicksight as the local timezone representation, not as UTC format. Can anyone advise what would be the best approach for this would be or if it is possible at all? It seems if I utilize a calculated field function like formatDate(), or even custom SQL of 'AT TIME ZONE' then my date columns are converted to strings. Then any attempt to convert these strings back to a date simply convert the date back to UTC format.
I have tried converting the returned date string with:
parseDate({NEWDATE}, "yyyy-MM-dd HH:mm:ss.SSS ZZZ", "Australia/Melbourne")
However, this keeps raising an error "This function does not have the correct number of arguments".
Any advice is appreciated.
amazon-web-services amazon-quicksight
asked Nov 9 at 23:00
apollocr
185
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
So reading through the AWS Quicksight docs, I have found the following information.
Handling Date Time Zones:
Amazon QuickSight uses UTC time for querying, filtering, and displaying date data.
When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.
I have a range of dates in my Athena dataset that I am analyzing in Quicksight. I would like to be able to view these dates in Quicksight as the local timezone representation, not as UTC format. Can anyone advise what would be the best approach for this would be or if it is possible at all? It seems if I utilize a calculated field function like formatDate(), or even custom SQL of 'AT TIME ZONE' then my date columns are converted to strings. Then any attempt to convert these strings back to a date simply convert the date back to UTC format.
I have tried converting the returned date string with:
parseDate({NEWDATE}, "yyyy-MM-dd HH:mm:ss.SSS ZZZ", "Australia/Melbourne")
However, this keeps raising an error "This function does not have the correct number of arguments".
Any advice is appreciated.
amazon-web-services amazon-quicksight
asked Nov 9 at 23:00
apollocr
185
So reading through the AWS Quicksight docs, I have found the following information.
Handling Date Time Zones:
Amazon QuickSight uses UTC time for querying, filtering, and displaying date data.
When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.
I have a range of dates in my Athena dataset that I am analyzing in Quicksight. I would like to be able to view these dates in Quicksight as the local timezone representation, not as UTC format. Can anyone advise what would be the best approach for this would be or if it is possible at all? It seems if I utilize a calculated field function like formatDate(), or even custom SQL of 'AT TIME ZONE' then my date columns are converted to strings. Then any attempt to convert these strings back to a date simply convert the date back to UTC format.
I have tried converting the returned date string with:
parseDate({NEWDATE}, "yyyy-MM-dd HH:mm:ss.SSS ZZZ", "Australia/Melbourne")
However, this keeps raising an error "This function does not have the correct number of arguments".
Any advice is appreciated.
amazon-web-services amazon-quicksight
amazon-web-services amazon-quicksight
asked Nov 9 at 23:00
apollocr
185
asked Nov 9 at 23:00
apollocr
185
edited Nov 10 at 1:01
asked Nov 9 at 23:00
apollocr
185
asked Nov 9 at 23:00
apollocr
185
asked Nov 9 at 23:00
apollocr
185
185
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00
add a comment |
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.
For example:
- Calculated filed name:
datetimemelbourne
- Formula:
addDateTime(11, 'HH', {datetime})
answered Nov 29 at 6:08
JcMaco
63011330
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',
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%2f53234336%2fdisplay-date-as-local-timzone-not-utc-in-aws-quicksight%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
up vote
0
down vote
I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.
For example:
- Calculated filed name:
datetimemelbourne
- Formula:
addDateTime(11, 'HH', {datetime})
answered Nov 29 at 6:08
JcMaco
63011330
add a comment |
up vote
0
down vote
I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.
For example:
- Calculated filed name:
datetimemelbourne
- Formula:
addDateTime(11, 'HH', {datetime})
answered Nov 29 at 6:08
JcMaco
63011330
add a comment |
up vote
0
down vote
up vote
0
down vote
I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.
For example:
- Calculated filed name:
datetimemelbourne
- Formula:
addDateTime(11, 'HH', {datetime})
answered Nov 29 at 6:08
JcMaco
63011330
I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.
For example:
- Calculated filed name:
datetimemelbourne
- Formula:
addDateTime(11, 'HH', {datetime})
answered Nov 29 at 6:08
JcMaco
63011330
answered Nov 29 at 6:08
JcMaco
63011330
answered Nov 29 at 6:08
JcMaco
63011330
answered Nov 29 at 6:08
JcMaco
63011330
63011330
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53234336%2fdisplay-date-as-local-timzone-not-utc-in-aws-quicksight%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
I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function.
– JcMaco
Nov 29 at 6:00