Ensure that date & relative date comparisons work in a consistent way across JSON, SQLite and MySQL
Open
Nobody has claimed this yet.
enhancement
- Dominant language
- JavaScript
- Stars
- 7
- Forks
- 7
- Avg merge
- 9h 4m
- Merged PRs (30d)
- 22
Description
NQL date handling is in an unmanageable state.
We have to manage across the following variables:
- The format you use to insert into a DB (e.g. in fixtures) is different to what DBs return
- The formats you can use to do a comparison like > or < is not consistent between SQLite and MySQL
- The formats we return from the DB, and from our API endpoints can all be different
- JSON comparisons seem to work in some cases and not others, but really we should be using ISODate
- The mai use cases for dates are:
- get helpers, where the real use case looks like this:
{{#get "posts" filter="published_at:<='{{published_at}}'+id:-{{id}}" limit="3"}}
this means it depends what comes out of the API as to what goes back in...
ref: https://ghost.org/docs/themes/helpers/get/#filter - Prev & Next helpers which are builtins that do essentially the same thing, but they enforce that the right format
ref: https://github.com/TryGhost/Ghost/blob/1cc38733ba6df8b8340420cff8fc7b643704c6ae/core/frontend/helpers/prev_post.js#L21 - relative date queries like
last_seen_at>now-1d
ref: https://github.com/TryGhost/NQL/issues/26
We need to make sure that dates work consistently in these 3 cases.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by comparing the date handling in JSON, SQLite and MySQL, then read core/frontend/helpers/prev_post.js and the linked NQL issue #26. Trace the get helper, Prev and Next helpers, and relative-date queries such as last_seen_at>now-1d. Done means these three use cases compare dates consistently across the supported database formats and API values.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mysql, sqlite
- Domain
- backend-api-design, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100