How to fetch DateTime from mysql using xdevapi
2
When I select a dateTime field, it gets returned as 6 bytes. How can I convert it to a time_point or struct tm?
mysqlx::Session session( "mysqlx://root:password@127.0.0.1:33060/catalog" );
auto row = session.sql( "select create_time from information_schema.tables order by 1 LIMIT 1" ).execute().fetchOne();
assert( row[0].getType()==8 );//raw type
assert( row[0].getRawBytes().second==6 );//6 bytes
var bytes = row[0].getRawBytes().first;
//e2 0f 08 0c 0a 32
//2018-08-12 10:50:04
c++ mysql mysql-connector
edited Nov 15 '18 at 21:39
Minding
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
add a comment |
2
When I select a dateTime field, it gets returned as 6 bytes. How can I convert it to a time_point or struct tm?
mysqlx::Session session( "mysqlx://root:password@127.0.0.1:33060/catalog" );
auto row = session.sql( "select create_time from information_schema.tables order by 1 LIMIT 1" ).execute().fetchOne();
assert( row[0].getType()==8 );//raw type
assert( row[0].getRawBytes().second==6 );//6 bytes
var bytes = row[0].getRawBytes().first;
//e2 0f 08 0c 0a 32
//2018-08-12 10:50:04
c++ mysql mysql-connector
edited Nov 15 '18 at 21:39
Minding
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
add a comment |
2
2
2
2
When I select a dateTime field, it gets returned as 6 bytes. How can I convert it to a time_point or struct tm?
mysqlx::Session session( "mysqlx://root:password@127.0.0.1:33060/catalog" );
auto row = session.sql( "select create_time from information_schema.tables order by 1 LIMIT 1" ).execute().fetchOne();
assert( row[0].getType()==8 );//raw type
assert( row[0].getRawBytes().second==6 );//6 bytes
var bytes = row[0].getRawBytes().first;
//e2 0f 08 0c 0a 32
//2018-08-12 10:50:04
c++ mysql mysql-connector
edited Nov 15 '18 at 21:39
Minding
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
When I select a dateTime field, it gets returned as 6 bytes. How can I convert it to a time_point or struct tm?
mysqlx::Session session( "mysqlx://root:password@127.0.0.1:33060/catalog" );
auto row = session.sql( "select create_time from information_schema.tables order by 1 LIMIT 1" ).execute().fetchOne();
assert( row[0].getType()==8 );//raw type
assert( row[0].getRawBytes().second==6 );//6 bytes
var bytes = row[0].getRawBytes().first;
//e2 0f 08 0c 0a 32
//2018-08-12 10:50:04
c++ mysql mysql-connector
c++ mysql mysql-connector
edited Nov 15 '18 at 21:39
Minding
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
edited Nov 15 '18 at 21:39
Minding
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
edited Nov 15 '18 at 21:39
Minding
331616
edited Nov 15 '18 at 21:39
Minding
331616
edited Nov 15 '18 at 21:39
Minding
331616
331616
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
asked Sep 2 '18 at 13:49
John DuffyJohn Duffy
3318
3318
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Hey I just spend 5 hours tring to figure out the same thing, the solution is to project the TIME/DATE/DATETIME field as a UNIX timestamp (integer) in your SQL Statement using UNIX_TIMESTAMP().
Then you can easily get the field as time_t (and optionally convert to a struct tm).
#include<time.h>
mysqlx::Session session{"mysqlx://root:password@127.0.0.1:33060/catalog"};
auto row = session.sql("SELECT UNIX_TIMESTAMP(create_time) FROM information_schema.tables ORDER BY 1 LIMIT 1").execute().fetchOne();
time_t creationTime = (int) row[0];
struct tm* creationTimePoint = localtime(creationTime);
Hope this helps. -Minding
answered Nov 15 '18 at 19:32
MindingMinding
331616
I encounter this error when implicitly convertingConversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguousand usingUNIX_TIMESTAMPit says that the value cannot be converted to an integer.
– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? IfUNIX_TIMESTAMPsays it can't convert the field to an integer, it's probably not a time related field.
– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type usingrow[0].getType() > 0. Thanks for the answer though.
– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
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');
});
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%2f52137654%2fhow-to-fetch-datetime-from-mysql-using-xdevapi%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
1
Hey I just spend 5 hours tring to figure out the same thing, the solution is to project the TIME/DATE/DATETIME field as a UNIX timestamp (integer) in your SQL Statement using UNIX_TIMESTAMP().
Then you can easily get the field as time_t (and optionally convert to a struct tm).
#include<time.h>
mysqlx::Session session{"mysqlx://root:password@127.0.0.1:33060/catalog"};
auto row = session.sql("SELECT UNIX_TIMESTAMP(create_time) FROM information_schema.tables ORDER BY 1 LIMIT 1").execute().fetchOne();
time_t creationTime = (int) row[0];
struct tm* creationTimePoint = localtime(creationTime);
Hope this helps. -Minding
answered Nov 15 '18 at 19:32
MindingMinding
331616
I encounter this error when implicitly convertingConversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguousand usingUNIX_TIMESTAMPit says that the value cannot be converted to an integer.
– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? IfUNIX_TIMESTAMPsays it can't convert the field to an integer, it's probably not a time related field.
– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type usingrow[0].getType() > 0. Thanks for the answer though.
– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
add a comment |
1
Hey I just spend 5 hours tring to figure out the same thing, the solution is to project the TIME/DATE/DATETIME field as a UNIX timestamp (integer) in your SQL Statement using UNIX_TIMESTAMP().
Then you can easily get the field as time_t (and optionally convert to a struct tm).
#include<time.h>
mysqlx::Session session{"mysqlx://root:password@127.0.0.1:33060/catalog"};
auto row = session.sql("SELECT UNIX_TIMESTAMP(create_time) FROM information_schema.tables ORDER BY 1 LIMIT 1").execute().fetchOne();
time_t creationTime = (int) row[0];
struct tm* creationTimePoint = localtime(creationTime);
Hope this helps. -Minding
answered Nov 15 '18 at 19:32
MindingMinding
331616
I encounter this error when implicitly convertingConversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguousand usingUNIX_TIMESTAMPit says that the value cannot be converted to an integer.
– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? IfUNIX_TIMESTAMPsays it can't convert the field to an integer, it's probably not a time related field.
– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type usingrow[0].getType() > 0. Thanks for the answer though.
– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
add a comment |
1
1
1
Hey I just spend 5 hours tring to figure out the same thing, the solution is to project the TIME/DATE/DATETIME field as a UNIX timestamp (integer) in your SQL Statement using UNIX_TIMESTAMP().
Then you can easily get the field as time_t (and optionally convert to a struct tm).
#include<time.h>
mysqlx::Session session{"mysqlx://root:password@127.0.0.1:33060/catalog"};
auto row = session.sql("SELECT UNIX_TIMESTAMP(create_time) FROM information_schema.tables ORDER BY 1 LIMIT 1").execute().fetchOne();
time_t creationTime = (int) row[0];
struct tm* creationTimePoint = localtime(creationTime);
Hope this helps. -Minding
answered Nov 15 '18 at 19:32
MindingMinding
331616
Hey I just spend 5 hours tring to figure out the same thing, the solution is to project the TIME/DATE/DATETIME field as a UNIX timestamp (integer) in your SQL Statement using UNIX_TIMESTAMP().
Then you can easily get the field as time_t (and optionally convert to a struct tm).
#include<time.h>
mysqlx::Session session{"mysqlx://root:password@127.0.0.1:33060/catalog"};
auto row = session.sql("SELECT UNIX_TIMESTAMP(create_time) FROM information_schema.tables ORDER BY 1 LIMIT 1").execute().fetchOne();
time_t creationTime = (int) row[0];
struct tm* creationTimePoint = localtime(creationTime);
Hope this helps. -Minding
answered Nov 15 '18 at 19:32
MindingMinding
331616
edited Jan 16 at 13:52
answered Nov 15 '18 at 19:32
MindingMinding
331616
answered Nov 15 '18 at 19:32
MindingMinding
331616
answered Nov 15 '18 at 19:32
MindingMinding
331616
331616
I encounter this error when implicitly convertingConversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguousand usingUNIX_TIMESTAMPit says that the value cannot be converted to an integer.
– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? IfUNIX_TIMESTAMPsays it can't convert the field to an integer, it's probably not a time related field.
– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type usingrow[0].getType() > 0. Thanks for the answer though.
– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
add a comment |
I encounter this error when implicitly convertingConversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguousand usingUNIX_TIMESTAMPit says that the value cannot be converted to an integer.
– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? IfUNIX_TIMESTAMPsays it can't convert the field to an integer, it's probably not a time related field.
– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type usingrow[0].getType() > 0. Thanks for the answer though.
– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
I encounter this error when implicitly converting
Conversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguous and using UNIX_TIMESTAMP it says that the value cannot be converted to an integer.– SagunKho
Jan 16 at 5:49
I encounter this error when implicitly converting
Conversion from 'mysqlx::Value' to 'time_t' (aka 'long') is ambiguous and using UNIX_TIMESTAMP it says that the value cannot be converted to an integer.– SagunKho
Jan 16 at 5:49
@SagunKho I added an explict cast to my answer, do you still encounter problems? If
UNIX_TIMESTAMP says it can't convert the field to an integer, it's probably not a time related field.– Minding
Jan 16 at 13:54
@SagunKho I added an explict cast to my answer, do you still encounter problems? If
UNIX_TIMESTAMP says it can't convert the field to an integer, it's probably not a time related field.– Minding
Jan 16 at 13:54
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type using
row[0].getType() > 0. Thanks for the answer though.– SagunKho
Jan 17 at 5:08
the issue was that the column was nullable and the value was null. It could not be converted to anything so I had to check the type using
row[0].getType() > 0. Thanks for the answer though.– SagunKho
Jan 17 at 5:08
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
Thanks, originally I casted to a string but UNIX_TIMESTAMP is better. Since the dates are in UTC, I use UNIX_TIMESTAMP(CONVERT_TZ(create_time, '+00:00', 'SYSTEM')).
– John Duffy
Jan 18 at 13:42
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');
});
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%2f52137654%2fhow-to-fetch-datetime-from-mysql-using-xdevapi%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
