Skip to content

Postgres should support relativeTime query #7746

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
3 tasks done
cbaker6 opened this issue Dec 27, 2021 · 4 comments · Fixed by #7747
Closed
3 tasks done

Postgres should support relativeTime query #7746

cbaker6 opened this issue Dec 27, 2021 · 4 comments · Fixed by #7747
Labels
state:released Released as stable version state:released-alpha Released as alpha version state:released-beta Released as beta version type:feature New feature or improvement of existing feature

Comments

@cbaker6
Copy link
Contributor

cbaker6 commented Dec 27, 2021

New Feature / Enhancement Checklist

Current Limitation

The PostgresAdapter doesn't support the relativeTime query constraint. Currently, the mongo adapter supports it.

Feature / Enhancement Description

Relative time queries allows devs to query dates based on strings, i.e. '1 day ago'

Example Use Case

Using the Swift SDK:

let constraint = relative("updatedAt" <= "3 days ago")
let query = GameScore.query(constraint)
do {
  let recentObjects = try await query.find()
  print(recentObjects)
} catch {
  // Handle error
}

Alternatives / Workarounds

Specify query constraints on dates directly.

3rd Party References

The parse-server already supports this for Mongo:

it_only_db('mongo')('should handle relative times correctly', function (done) {
const now = Date.now();
const obj1 = new Parse.Object('MyCustomObject', {
name: 'obj1',
ttl: new Date(now + 2 * 24 * 60 * 60 * 1000), // 2 days from now
});
const obj2 = new Parse.Object('MyCustomObject', {
name: 'obj2',
ttl: new Date(now - 2 * 24 * 60 * 60 * 1000), // 2 days ago
});
Parse.Object.saveAll([obj1, obj2])
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: 'in 1 day' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(1);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: '1 day ago' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(1);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.lessThan('ttl', { $relativeTime: '5 days ago' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(0);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: '3 days ago' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(2);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: 'now' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(1);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: 'now' });
q.lessThan('ttl', { $relativeTime: 'in 1 day' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(0);
})
.then(() => {
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: '1 year 3 weeks ago' });
return q.find({ useMasterKey: true });
})
.then(results => {
expect(results.length).toBe(2);
})
.then(done, done.fail);
});
it_only_db('mongo')('should error on invalid relative time', function (done) {
const obj1 = new Parse.Object('MyCustomObject', {
name: 'obj1',
ttl: new Date(Date.now() + 2 * 24 * 60 * 60 * 1000), // 2 days from now
});
const q = new Parse.Query('MyCustomObject');
q.greaterThan('ttl', { $relativeTime: '-12 bananas ago' });
obj1
.save({ useMasterKey: true })
.then(() => q.find({ useMasterKey: true }))
.then(done.fail, () => done());
});
it_only_db('mongo')('should error when using $relativeTime on non-Date field', function (done) {
const obj1 = new Parse.Object('MyCustomObject', {
name: 'obj1',
nonDateField: 'abcd',
ttl: new Date(Date.now() + 2 * 24 * 60 * 60 * 1000), // 2 days from now
});
const q = new Parse.Query('MyCustomObject');
q.greaterThan('nonDateField', { $relativeTime: '1 day ago' });
obj1
.save({ useMasterKey: true })
.then(() => q.find({ useMasterKey: true }))
.then(done.fail, () => done());
});

@parse-github-assistant
Copy link

Thanks for opening this issue!

  • 🎉 We are excited about your ideas for improvement!

@mtrezza mtrezza added the type:feature New feature or improvement of existing feature label Jan 1, 2022
@parseplatformorg
Copy link
Contributor

🎉 This change has been released in version 5.0.0-alpha.14

@parseplatformorg parseplatformorg added the state:released-alpha Released as alpha version label Jan 2, 2022
@parseplatformorg
Copy link
Contributor

🎉 This change has been released in version 5.0.0-beta.10

@parseplatformorg parseplatformorg added the state:released-beta Released as beta version label Mar 15, 2022
@parseplatformorg
Copy link
Contributor

🎉 This change has been released in version 5.1.0

@parseplatformorg parseplatformorg added the state:released Released as stable version label Mar 18, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
state:released Released as stable version state:released-alpha Released as alpha version state:released-beta Released as beta version type:feature New feature or improvement of existing feature
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants