0

I'm after uploading a dataset to MongoDB with some fields as time. When these were uploaded they went in as strings. Like TOC below:

_id:5dd1a91511a7e24b487bce43 
Unit:"Fire" 
Date:"01-Jan-13" 
TOC:"01:27:19"

I am trying to change these using the Aggregations tool in Compass. I have matched all documents where TOC is not a timestamp:

$match
/**
 * query - The query in MQL.
 */
{
  TOC: {$not: {$type: 17}}
}

And I am then trying to convert the string to be read as time instead of a string:

$project
/**
 * specifications - The fields to
 *   include or exclude.
 */
{
  TOC: {
         "$dateFromString": {
            "dateString": '$TOC',
            "format": "%H:%M:%S"
         }
      }
}

Getting the error:

an incomplete date/time string has been found, with elements missing: "01:27:19"

Any idea how I can fix this, or if there is a better way to do it?

Learner
  • 1
  • 1
  • 1
    The error is because the input for the `$dateFromString` must have _date_ values (with year, month and day). There can be both date and time values, or just date values, but not just time values only. – prasad_ Nov 18 '19 at 04:22
  • Cheers for that. So what do I need to use to change string into time for just time? – Learner Nov 18 '19 at 12:52
  • I don't see anything off-hand. But, try searching the net for that. If you don't find anything, you may have to add some _dummy_ date and make it a `Date`. Some trick, maybe. It is unusual to store just time. Usually, the time and date are stored together as a `Date` field (or sometimes a string). – prasad_ Nov 18 '19 at 13:54
  • Ya, the dataset has the date in one field and then the time fields I'm working with just log the times of calls. So call 1 say at 13:55:28 and call 2 at 14:03:25. I just want to convert to time so I can subtract one from the other and get the time between calls. Would you have any suggestion on is there another way rather than converting then? Cheers for the answers by the way. – Learner Nov 18 '19 at 15:46
  • You may not get the correct time difference if you subtract time only. How will you subtract time only which crosses a day? For that you will need the date also. So, it must be date+time. And, it needs to be stored as a [Date](https://docs.mongodb.com/manual/reference/method/Date/index.html) object. Then a simple subtraction will get time difference. See these [operators](https://docs.mongodb.com/manual/reference/operator/aggregation/#date-expression-operators) which can be used to work with date+time. You can use the present date and time strings together to get the functionality. – prasad_ Nov 19 '19 at 01:16
  • Ya, I concatenated the date field to all the time fields and was able to subtract then. Only issue is we only have one date in the data so doesn't account for calls passing over to the next day. Thanks for your help – Learner Nov 19 '19 at 10:10

1 Answers1

-1

For times that are on the same day:

            {
            $set: {
                dStart: {
                    $dateFromString: {
                        dateString: { $concat: ['1970-01-01T', '$schedules.start'] },
                        format: '%Y-%m-%dT%H:%M'
                    }
                }
            }
        },
        {
            $set: {
                dEnd: {
                    $dateFromString: {
                        dateString: { $concat: ['1970-01-01T', '$schedules.end'] },
                        format: '%Y-%m-%dT%H:%M'
                    }
                }
            }
        },
        {
            $group: {
                _id: '$doctor',
                totalMinutes: {
                    $sum: { $divide: [{ $subtract: ['$dStart', '$dEnd'] }, -60000] }
                }

            }
        },
        {
            $project: {
                _id: 1,
                totalMinutes: 1
            }
        }
Augusto César
  • 37
  • 1
  • 1