Records with invalid date are not displayed in MongoDB Atlas but on Mongo Shell they do

I recently created program for esp8266 that is reading data from temperature sensor. Sometimes the date obtained from ntp server is bad and it sends it to server and then to db. Records with this invalid date are not visible on MongoDB Atlas. To manage them I need to use MongoShell. It should be possible to manage this via website.

How it looks on website and on mongo shell:

There are visible empty divs which should contain data

@Marcin_R1,

Can you copy and paste the results from your .find() command here? I’m going to try replicate this behaviour in my own test environment so I will copy and paste the documents to insert them.

Regards,
Jason

Here are results of find() command:

Atlas atlas-6nd759-shard-0 [primary] Cluster0> db.measure.find({ stationId: "2767224" })
[
  {
    _id: ObjectId("6474e17f1b4da771c3d2e9a9"),
    stationId: '2767224',
    date: ISODate("2023-09-26T21:23:54.785Z"),
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("57.99000168"),
    pressure: Decimal128("99342.46094"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("6474e1e6fe31b663ef403ff5"),
    stationId: '2767224',
    date: ISODate("2023-09-26T21:23:54.785Z"),
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("57.99000168"),
    pressure: Decimal128("99342.46094"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a311b3be07117fd1f56f9"),
    stationId: '2767224',
    date: ISODate("2023-06-02T18:12:41.000Z"),
    temp: Decimal128("21.18000031"),
    humidity: Decimal128("62.83000183"),
    pressure: Decimal128("993.1099854"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a2a11ea628d0c97947052"),
    stationId: '2767224',
    date: ISODate("2023-06-02T17:42:39.000Z"),
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("64.83999634"),
    pressure: Decimal128("992.5200195"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a29f9ac10446e49226371"),
    stationId: '2767224',
    date: ISODate("2023-06-02T17:42:15.000Z"),
    temp: Decimal128("21.29999924"),
    humidity: Decimal128("64.66999817"),
    pressure: Decimal128("992.5700073"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a273e2155c9771458b11f"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.54999924"),
    humidity: Decimal128("64.36000061"),
    pressure: Decimal128("99231.79688"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647b54bb3f56fc2b8ecc2f87"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.70000076"),
    humidity: Decimal128("45.99000168"),
    pressure: Decimal128("99633.72656"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a27fe3cc3a06ef79d3c53"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.01000023"),
    humidity: Decimal128("64.73999786"),
    pressure: Decimal128("99236.9375"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a27e83cc3a06ef79d3c52"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.35000038"),
    humidity: Decimal128("64.44000244"),
    pressure: Decimal128("99238.36719"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a27503cc3a06ef79d3c51"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.21999931"),
    humidity: Decimal128("64.47000122"),
    pressure: Decimal128("99228.45313"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a26bec69dca134631f619"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("65.20999908"),
    pressure: Decimal128("99231.4375"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a26a1c69dca134631f618"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.28000069"),
    humidity: Decimal128("65.01000214"),
    pressure: Decimal128("99230.9375"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a25925835c010029f14a4"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("64.91999817"),
    pressure: Decimal128("99215.38281"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a25855835c010029f14a3"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.29000092"),
    humidity: Decimal128("64.72000122"),
    pressure: Decimal128("99221.71875"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a2371af63a071e34458e7"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.97999954"),
    humidity: Decimal128("65.12000275"),
    pressure: Decimal128("99200.52344"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a235faf63a071e34458e6"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("21.29000092"),
    humidity: Decimal128("65.15000153"),
    pressure: Decimal128("99204.32031"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a22bc3ef30a66f882244c"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.73999977"),
    humidity: Decimal128("66.05999756"),
    pressure: Decimal128("99193.78125"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("647a20d4bb55e94322cacb36"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.69000053"),
    humidity: Decimal128("66.06999969"),
    pressure: Decimal128("99184.96875"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("6474fcf496474f714a6b0e59"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.63999939"),
    humidity: Decimal128("66.30999756"),
    pressure: Decimal128("99288.35938"),
    _class: 'com.weather.server.domain.entity.Measure'
  },
  {
    _id: ObjectId("6474f5e9047d8d60cfd5cb35"),
    stationId: '2767224',
    date: Invalid Date,
    temp: Decimal128("20.46999931"),
    humidity: Decimal128("65.48000336"),
    pressure: Decimal128("99278.59375"),
    _class: 'com.weather.server.domain.entity.Measure'
  }
]

I don’t know if it is possible to insert document with invalid date via cli, I know that on website it’s not possible.
You might have to use example server app to reproduce issue - I have spring boot server which connects to Mongodb. If you would like to use it I can provide link to github.

Example bad date send to server which results in not-displayable document is:
-5149169-04-23T14:56:57Z

Currently I fixed my esp8266 program so I now have right date, but still it should be possible to delete documents with bad data via website.

1 Like

Thanks for providing those examples. I can see to some degree what you mean now. I was not able to insert via mongosh an invalid date for example but was able to bring up the Invalid Date value:

test> var test = new Date("-5149169-04-23T14:56:57Z")

test> test
Invalid Date

Inserting documents with this value seem to revert to a default of ISODate("1970-01-01T00:00:00.000Z") instead of Invalid Date.

Great - My initial thoughts were to perhaps filter / cleanse or implement a validator to have the correct date formats to limit results with Invalid Date from being inserted which you have done so. I will investigate the document(s) not displaying in the Atlas Data Explorer UI when a document contains a field with the value Invalid Date.

Could you also share the spring boot driver versions and command used to insert the document?

I’ll update here accordingly.

Regards,
Jason

It looks according to pom.xml that I have springboot in version 2.6.4, same for spring-boot-starter-data-mongodb artifact. Command that I use to insert document is just save() called on repository, here is method from MeasureServiceImpl.java:

@Override
    public boolean saveMeasure(NewMeasureDto newMeasureDto) {
        if(verifyStationId(newMeasureDto.getStationId())) {
            Measure measure = newMeasureMapper.mapToEntity(newMeasureDto);
            measureRepository.save(measure);
            return true;
        }
        else{
            return false;
        }
    }

If you would like to take a look at code, here is link to repo: https://github.com/4meters/weather-server

I checked the console and there is range error for each document with invalid date:

1 Like

Do you have an example of the value of measure here?

I’m utilising save in the spring boot test environment but when using "-5149169-04-23T14:56:57Z" the document does not get inserted (instead there is a server error).

Regards,
Jason

I’m posting mapToEntity function, it may be the key to solution:

@Component
public class NewMeasureMapper {

    public Measure mapToEntity(NewMeasureDto newMeasureDto){
        Measure measure = new Measure();
        measure.setStationId(newMeasureDto.getStationId());
        measure.setDate(Date.from(Instant.parse(newMeasureDto.getDate())));

        if(newMeasureDto.getTemp()!=null){
            measure.setTemp(Decimal128.parse(newMeasureDto.getTemp()));
        }
        if(newMeasureDto.getHumidity()!=null){
            measure.setPressure(Decimal128.parse(newMeasureDto.getPressure()));
        }
        if(newMeasureDto.getPressure()!=null){
            measure.setHumidity(Decimal128.parse(newMeasureDto.getHumidity()));
        }
        if(newMeasureDto.getPm10()!=null){
            measure.setPm10(Decimal128.parse(newMeasureDto.getPm10()));
        }
        if(newMeasureDto.getPm25()!=null){
            measure.setPm25(Decimal128.parse(newMeasureDto.getPm25()));
        }
        if(newMeasureDto.getPm25Corr()!=null){
            measure.setPm25Corr(Decimal128.parse(newMeasureDto.getPm25Corr()));
        }
        
        return measure;
    }
}

The most important line is this:

measure.setDate(Date.from(Instant.parse(newMeasureDto.getDate())));

Before setting date in document it’s parsed, maybe if you use it that way, you will manage to save document.
You can replace “newMeasureDto.getDate()” with “-5149169-04-23T14:56:57Z”

I have added System.out.println(measure) to saveMeasure method, example value of measure:

Measure{_id='null', stationId='2767224', date=Wed Jan 17 15:56:57 CET 5149064, temp=20.78000069, humidity=72.36000061, pressure=992.15, pm25=null, pm25Corr=null, pm10=null}

It looks that after parsing date change from negative number to positive.

1 Like