$gt (compare time) operator

[bwgjoseph]

Hi,

How do I perform a date comparison filter using $gt operator?

Something that I also noticed the difference between storing date in mongodb and couchbase (please correct me if I'm wrong) is that in mongo, it wraps Date in a ISODate helper and is stored in UTC format. Can read it here, and it seem that couchbase also stores in UTC but is a string type.

So looking at the $gt test case, how can I query (filter) date greater than, less than, between a date/date range? Would love to have some examples.

Thanks!

[AV25242]

Will get back to you on this

[AV25242]

@bwgjoseph

The team came back with this Yes date is string type and the example is as below, with an exception of between there is a bug and an Issue will be created to address it

test('Test gt lt between date operator', async () => {
    const schema = new Schema({ name: String, date: { type: Date } });
    const DummyDate = model('DummyDate', schema);
    await startInTest(getDefaultConnection());
    const result = await DummyDate.create({ name: 'Dummy', date: new Date('10 Dec 2020 00:00') });
    expect(result.id).toBeDefined();
    const documents = await DummyDate.find({
      $or: [
        { date: { $gt: '2020-12-01', $btw: ['2020-12-01', '2020-12-11'] } },
        { date: { $lt: '2020-12-11' } },
        { date: { $btw: ['2020-12-01', '2020-12-11'] } },
      ],
    });
    expect(documents.rows).toBeDefined();
    // SELECT `travel-sample`.*,collectionName FROM `travel-sample` WHERE ((date>"2020-12-01" AND date BETWEEN "2020-12-01" AND "2020-12-11") OR date<"2020-12-11" OR date BETWEEN "2020-12-01" AND "2020-12-11") AND scopeName="_default" AND collectionName="DummyDate"
  });

[bwgjoseph]

Interesting findings...

1. I got parsing failure when running with your example

const schema = new ottoman.Schema({ name: String, date: { type: Date } });
        const DummyDate = ottoman.model('DummyDate', schema);
        const result = await DummyDate.create({ name: 'Dummy', date: new Date('10 Dec 2020 00:00') });
        const documents = await DummyDate.find({
            $or: [
                { date: { $gt: '2020-12-01', $btw: ['2020-12-01', '2020-12-11'] } },
                { date: { $lt: '2020-12-11' } },
                { date: { $btw: ['2020-12-01', '2020-12-11'] } },
            ],
        });

Error: parsing failure
      at _getWrappedErr (node_modules\couchbase\lib\errors.js:830:14)
      at Object.wrapLcbErr (node_modules\couchbase\lib\errors.js:1009:20)
      at Z:\bwgjoseph\mongoose-vs-ottoman\node_modules\couchbase\lib\queryexecutor.js:126:26

I think it is because it actually doesn't accept 01, so it has to be 2020-12-1

---

2. Different type returned from create and find

If you log out the output from create, it seem that the date returned is in Date object

const result = await DummyDate.create({ name: 'Dummy', date: new Date('10 Dec 2020 00:00') });
console.log(result);

But if you get the output from find, then it will be in string

const options = { consistency: SearchConsistency.LOCAL };
console.log(await DummyDate.find({}, options));

---

3. So it seem like $gt still doesn't work correctly, or am I doing it wrong?

const schema = new ottoman.Schema({ name: String, date: { type: Date } });
const DummyDate = ottoman.model('DummyDate', schema);
await DummyDate.create({ name: 'Dummy', date: new Date('10 Dec 2020 00:00') });
const options = { consistency: SearchConsistency.LOCAL };

const doc = await DummyDate.find({
    date: {
        $gt: '2021-12-11'
    },
}, options);

console.log(doc);

Result is still coming out. It should not have any result?

And when I tried $lt, the result actually reserved.

const doc = await DummyDate.find({
    date: {
        $lt: '2021-12-11'
    },
}, options);

So this should have result, but it doesn't return anything

---

4. In addition, can I put in 2020-12-09T16:00:00.000Z to search instead? It doesn't allow me to at the moment, giving the parsing failure, similar to Item 1

In general, when I run a search, developer usually would pass back the date from the document or construct using Date(), or maybe some other means. For example...

const doc = await model.find(); // assuming return some doc with Date field
const filterDoc = await model2.find({ date: { $gt: doc.date }}); // doc.date would be 2020-12-09T16:00:00.000Z
// or
const filterDoc = await model2.find({ date: { $gt: new Date().toISOString() }}); // 2020-12-03T14:31:51.229Z

So if the date has to be in a very specific format, then it wouldn't be intuitive to use, and very 'restrictive'

[AV25242]

Thanks @bwgjoseph developers are looking into it ATM

[AV25242]

Hey @bwgjoseph we pushed a new release apha.10 can you get that. It fixed a lot of these operator behaviours as well.

[bwgjoseph]

Hi,

You can refer to the commit, and everything seem to be working fine now, with exception to the return type as seem in verify gh-#21 p2 - return type test case

Any reason why the return type is not consistent?

[AV25242]

Hi @bwgjoseph would you please elaborate what you were seeing with that particular use case ?

[bwgjoseph]

@AV25242 It's mentioned above

2. Different type returned from create and find

If you log out the output from create, it seem that the date returned is in Date object

const result = await DummyDate.create({ name: 'Dummy', date: new Date('10 Dec 2020 00:00') });
console.log(result);

But if you get the output from find, then it will be in string

const options = { consistency: SearchConsistency.LOCAL };
console.log(await DummyDate.find({}, options));

[AV25242]

Thanks @bwgjoseph let me get back

[AV25242]

@bwgjoseph , Dates when stored in Couchbase are serialized and stored as string, that's the reason why a find gets you back a String. On the other hand a Create doesnt get the data from the database so in your example const result gives you back the data that that's in memory and not from the database and that's why you see a string.

[bwgjoseph]

I understand what you're saying but IMO, this is a inconsistency from ottoman (plainly speaking from a normal developer point-of-view). As a developer, I would expect it to return me consistent result whether I run a create or find API call. Otherwise, this would cause me (as developer) to "guess" the result (field type).

Even if I do const result = await DummyDate.create({ name: 'Dummy', date: '10 Dec 2020 00:00' });, the return type is still a date and not string. Hence, I guess the return data type follows the model, and a find doesn't follow.

Is there any way to change the return type to be consistent? And if there are any others that returns differently?

[AV25242]

@bwgjoseph by all means

Created an issue https://github.com/couchbaselabs/node-ottoman/issues/330

[bwgjoseph]

Thanks!

Based on description, would that mean that couchbase would continue to be storing as string but whenever I use ottoman to query, it will always return Date type? And if that's correct, then I assume that whether the query is via CRUD operations, or via n1q1, it would all return the same Date type? Or is it not?

// example
getDefaultConnection().query('SELECT * from testBucket')
// Would it return a `string` or `Date` type for the date field

[bwgjoseph]

I did a quick test, and it seem like the return type is string

const x = await getDefaultConnection().query('SELECT * from testBucket');
console.log(x.rows[0]);

// output
{
  testBucket: {
    callsign: 'Hawk',
    name: 'Couchbase Airlines',
    operational: true,
    destination: [ 'Japan' ],
    scheduledAt: '2020-11-20T03:30:00.000Z',
    capacity: 250,
    model: 'A380',
    size: 'S',
    info: {
      firstFlightAt: '2010-01-12T05:10:00.000Z',
      numberOfFlightsSince: 10000
    },
    id: '25d78a4f-f807-4819-a234-09da6a39f9a8',
    _type: 'Airplane',
    _scope: '_default'
  }
}

[AV25242]

@bwgjoseph thanks

[bwgjoseph]

This has been verified and will be closed now

But note on the last point where using query will still return as string where ottoman returns as Date