Convert date string to ISO format issue mongodb
I am joining two tables together by employee name. I have that part working. The issue that I have currently is that I need the date and time to be ISO format and I need to do that within my aggregation. I cant figure out how.
I need to convert:
2018-11-03:10:47:31
To:
2018-11-03T10:47:31.000Z
employee collection
{
"_id" : ObjectId("(Object ID here"),
"name": "Test"
}
clock collection
{
"_id" : ObjectId("(Object ID here"),
"employeeName": "Test",
"time": "2014-11-21:17:15:00"
}
Here is my join
dbo.collection("employee").aggregate([
{ "$lookup": {
"localField": "name",
"from": "clock",
"foreignField": "clock.employeeName",
"as": "employeeInfo"
}
}]).toArray(function(err, results) {
console.log(results);
});
arrays mongodb mongoose mongodb-query aggregation-framework
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
|
show 5 more comments
I am joining two tables together by employee name. I have that part working. The issue that I have currently is that I need the date and time to be ISO format and I need to do that within my aggregation. I cant figure out how.
I need to convert:
2018-11-03:10:47:31
To:
2018-11-03T10:47:31.000Z
employee collection
{
"_id" : ObjectId("(Object ID here"),
"name": "Test"
}
clock collection
{
"_id" : ObjectId("(Object ID here"),
"employeeName": "Test",
"time": "2014-11-21:17:15:00"
}
Here is my join
dbo.collection("employee").aggregate([
{ "$lookup": {
"localField": "name",
"from": "clock",
"foreignField": "clock.employeeName",
"as": "employeeInfo"
}
}]).toArray(function(err, results) {
console.log(results);
});
arrays mongodb mongoose mongodb-query aggregation-framework
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
1
The problem is that your date format is weird and you could do this with$dateFromStringbut itsformatoption is only available in mongoDB 4.0 and up.
– Akrion
Nov 28 '18 at 22:45
1
OK then simply look into either doing something like$unwindand then$addFieldswhere inside you would do something like"employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.
– Akrion
Nov 28 '18 at 22:51
1
The idea is to use theformatoption of$dateFromStringsince your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it tonew Date()
– Akrion
Nov 28 '18 at 22:53
|
show 5 more comments
I am joining two tables together by employee name. I have that part working. The issue that I have currently is that I need the date and time to be ISO format and I need to do that within my aggregation. I cant figure out how.
I need to convert:
2018-11-03:10:47:31
To:
2018-11-03T10:47:31.000Z
employee collection
{
"_id" : ObjectId("(Object ID here"),
"name": "Test"
}
clock collection
{
"_id" : ObjectId("(Object ID here"),
"employeeName": "Test",
"time": "2014-11-21:17:15:00"
}
Here is my join
dbo.collection("employee").aggregate([
{ "$lookup": {
"localField": "name",
"from": "clock",
"foreignField": "clock.employeeName",
"as": "employeeInfo"
}
}]).toArray(function(err, results) {
console.log(results);
});
arrays mongodb mongoose mongodb-query aggregation-framework
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
I am joining two tables together by employee name. I have that part working. The issue that I have currently is that I need the date and time to be ISO format and I need to do that within my aggregation. I cant figure out how.
I need to convert:
2018-11-03:10:47:31
To:
2018-11-03T10:47:31.000Z
employee collection
{
"_id" : ObjectId("(Object ID here"),
"name": "Test"
}
clock collection
{
"_id" : ObjectId("(Object ID here"),
"employeeName": "Test",
"time": "2014-11-21:17:15:00"
}
Here is my join
dbo.collection("employee").aggregate([
{ "$lookup": {
"localField": "name",
"from": "clock",
"foreignField": "clock.employeeName",
"as": "employeeInfo"
}
}]).toArray(function(err, results) {
console.log(results);
});
arrays mongodb mongoose mongodb-query aggregation-framework
arrays mongodb mongoose mongodb-query aggregation-framework
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
asked Nov 28 '18 at 22:16
HeelMegaHeelMega
768
768
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
1
The problem is that your date format is weird and you could do this with$dateFromStringbut itsformatoption is only available in mongoDB 4.0 and up.
– Akrion
Nov 28 '18 at 22:45
1
OK then simply look into either doing something like$unwindand then$addFieldswhere inside you would do something like"employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.
– Akrion
Nov 28 '18 at 22:51
1
The idea is to use theformatoption of$dateFromStringsince your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it tonew Date()
– Akrion
Nov 28 '18 at 22:53
|
show 5 more comments
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
1
The problem is that your date format is weird and you could do this with$dateFromStringbut itsformatoption is only available in mongoDB 4.0 and up.
– Akrion
Nov 28 '18 at 22:45
1
OK then simply look into either doing something like$unwindand then$addFieldswhere inside you would do something like"employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.
– Akrion
Nov 28 '18 at 22:51
1
The idea is to use theformatoption of$dateFromStringsince your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it tonew Date()
– Akrion
Nov 28 '18 at 22:53
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
1
1
The problem is that your date format is weird and you could do this with
$dateFromString but its format option is only available in mongoDB 4.0 and up.– Akrion
Nov 28 '18 at 22:45
The problem is that your date format is weird and you could do this with
$dateFromString but its format option is only available in mongoDB 4.0 and up.– Akrion
Nov 28 '18 at 22:45
1
1
OK then simply look into either doing something like
$unwind and then $addFields where inside you would do something like "employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.– Akrion
Nov 28 '18 at 22:51
OK then simply look into either doing something like
$unwind and then $addFields where inside you would do something like "employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.– Akrion
Nov 28 '18 at 22:51
1
1
The idea is to use the
format option of $dateFromString since your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it to new Date()– Akrion
Nov 28 '18 at 22:53
The idea is to use the
format option of $dateFromString since your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it to new Date()– Akrion
Nov 28 '18 at 22:53
|
show 5 more comments
1 Answer
1
active
oldest
votes
Since your date is in very weird format 2018-11-03:10:47:31 you can't easily parse it with mongo prior version 4.0. The reason for this is that in 4.0 the format option was added to the $dateFromString function which allows you to specify random date format which to parse with. So as you mention if 4.0 is an option then you would do something like this:
Adding a field to your aggregation after you unwind the employeeInfo:
$addFields: {
"employeeInfo.date": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
Or projecting it in your project pipeline.
{
"$project": {
"employeeInfo.time": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
}
The project approach might need the unwind before hand as well since it might be picky on the fact that you have an array as employeeInfo
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
add a comment |
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%2f53528935%2fconvert-date-string-to-iso-format-issue-mongodb%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
Since your date is in very weird format 2018-11-03:10:47:31 you can't easily parse it with mongo prior version 4.0. The reason for this is that in 4.0 the format option was added to the $dateFromString function which allows you to specify random date format which to parse with. So as you mention if 4.0 is an option then you would do something like this:
Adding a field to your aggregation after you unwind the employeeInfo:
$addFields: {
"employeeInfo.date": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
Or projecting it in your project pipeline.
{
"$project": {
"employeeInfo.time": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
}
The project approach might need the unwind before hand as well since it might be picky on the fact that you have an array as employeeInfo
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
add a comment |
Since your date is in very weird format 2018-11-03:10:47:31 you can't easily parse it with mongo prior version 4.0. The reason for this is that in 4.0 the format option was added to the $dateFromString function which allows you to specify random date format which to parse with. So as you mention if 4.0 is an option then you would do something like this:
Adding a field to your aggregation after you unwind the employeeInfo:
$addFields: {
"employeeInfo.date": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
Or projecting it in your project pipeline.
{
"$project": {
"employeeInfo.time": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
}
The project approach might need the unwind before hand as well since it might be picky on the fact that you have an array as employeeInfo
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
add a comment |
Since your date is in very weird format 2018-11-03:10:47:31 you can't easily parse it with mongo prior version 4.0. The reason for this is that in 4.0 the format option was added to the $dateFromString function which allows you to specify random date format which to parse with. So as you mention if 4.0 is an option then you would do something like this:
Adding a field to your aggregation after you unwind the employeeInfo:
$addFields: {
"employeeInfo.date": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
Or projecting it in your project pipeline.
{
"$project": {
"employeeInfo.time": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
}
The project approach might need the unwind before hand as well since it might be picky on the fact that you have an array as employeeInfo
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
Since your date is in very weird format 2018-11-03:10:47:31 you can't easily parse it with mongo prior version 4.0. The reason for this is that in 4.0 the format option was added to the $dateFromString function which allows you to specify random date format which to parse with. So as you mention if 4.0 is an option then you would do something like this:
Adding a field to your aggregation after you unwind the employeeInfo:
$addFields: {
"employeeInfo.date": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
Or projecting it in your project pipeline.
{
"$project": {
"employeeInfo.time": {
$dateFromString: {
dateString: "$employeeInfo.time",
format: "%Y-%m-%d:%H:%M:%S"
}
}
}
}
The project approach might need the unwind before hand as well since it might be picky on the fact that you have an array as employeeInfo
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
answered Nov 28 '18 at 23:01
AkrionAkrion
9,57011224
9,57011224
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.
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%2f53528935%2fconvert-date-string-to-iso-format-issue-mongodb%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
What is your mongoDB version?
– Akrion
Nov 28 '18 at 22:24
I am using mongo 3.6
– HeelMega
Nov 28 '18 at 22:25
1
The problem is that your date format is weird and you could do this with
$dateFromStringbut itsformatoption is only available in mongoDB 4.0 and up.– Akrion
Nov 28 '18 at 22:45
1
OK then simply look into either doing something like
$unwindand then$addFieldswhere inside you would do something like"employeeInfo.date": { $dateFromString: { dateString: "$employeeInfo.time", format: "%Y-%m-%d:%H:%M:%S" } }. I do not have 4.0 handy right now to give you the full query.– Akrion
Nov 28 '18 at 22:51
1
The idea is to use the
formatoption of$dateFromStringsince your date format is completely messed up and mongoDB does not recognize it by default nor when you pass it tonew Date()– Akrion
Nov 28 '18 at 22:53