Suppose I have a datascript variable ?reference-date that is bound to an OG date such as the integer 20200131. How can I perform date arithmetic so that I can query time ranges relative to that date? For example, I want to do something like this:
:where
...omitted: something that binds ?record-date and ?reference-date
;; Keep records whose ?record-date is within 60 days (plus or minus) of ?reference-date.
[(<= (add-duration-to-date ?reference-date "-60d") ?record-date)]
[(< ?record-date (add-duration-to-date ?reference-date "60d"))]
If I just do something like [(<= (- ?reference-date 60) ?record-date)] then I end up comparing ?record-date to meaningless “dates” like 20200071.
Real date arithmetic is not supported. However, developing workarounds is possible. For example, the following code produces variables ?from-date and ?to-date as integer dates 2 months (i.e. about 60 days) before and after the ?reference-date respectively:
[(str ?reference-date) ?ymd]
[(subs ?ymd 0 4) ?yyyy]
[(subs ?ymd 4 6) ?mm]
[(subs ?ymd 6 8) ?dd]
[(* 1 ?yyyy) ?y]
[(* 1 ?mm) ?m]
[(* 1 ?dd) ?d]
(or-join [?from-y ?from-m ?y ?m ?to-y ?to-m]
(and
[(< ?m 3)]
[(- ?y 1) ?from-y]
[(- ?y 0) ?to-y]
[(+ ?m 10) ?from-m]
[(+ ?m 2) ?to-m]
)
(and
[(> ?m 2)]
[(< ?m 11)]
[(- ?y 0) ?from-y]
[(+ ?y 0) ?to-y]
[(- ?m 2) ?from-m]
[(+ ?m 2) ?to-m]
)
(and
[(> ?m 10)]
[(+ ?y 0) ?from-y]
[(+ ?y 1) ?to-y]
[(- ?m 2) ?from-m]
[(- ?m 10) ?to-m]
)
)
[(* ?from-y 10000) ?from-y0000]
[(* ?from-m 100) ?from-m00]
[(+ ?from-y0000 ?from-m00 ?d) ?from-date]
[(* ?to-y 10000) ?to-y0000]
[(* ?to-m 100) ?to-m00]
[(+ ?to-y0000 ?to-m00 ?d) ?to-date]
1 Like
Thanks, that is very handy. Suppose I wanted exactly 60 days (or some other arbitrary number of days) before and after the reference date, accommodating leap years. Is that possible? Perhaps via a function defined in custom.js?
I had an idea—I think I might be able to define a recursive rule. I’ll try that out and report back if it works.
OK, I think these rules work:
:rules [
;; Leap year every 4 years except every 100 years except every 400 years.
[
(feb-days ?y ?febdays)
[(mod ?y 400) ?mod400]
[(= ?mod400 0)]
[(ground 29) ?febdays]
]
[
(feb-days ?y ?febdays)
[(mod ?y 400) ?mod400]
[(!= ?mod400 0)]
[(mod ?y 100) ?mod100]
[(= ?mod100 0)]
[(ground 28) ?febdays]
]
[
(feb-days ?y ?febdays)
[(mod ?y 400) ?mod400]
[(!= ?mod400 0)]
[(mod ?y 100) ?mod100]
[(!= ?mod100 0)]
[(mod ?y 4) ?mod4]
[(= ?mod4 0)]
[(ground 29) ?febdays]
]
[
(feb-days ?y ?febdays)
[(mod ?y 400) ?mod400]
[(!= ?mod400 0)]
[(mod ?y 100) ?mod100]
[(!= ?mod100 0)]
[(mod ?y 4) ?mod4]
[(!= ?mod4 0)]
[(ground 28) ?febdays]
]
[
(month-days ?y ?m ?mdays)
[(= ?m 2)]
(feb-days ?y ?mdays)
]
[
(month-days ?y ?m ?mdays)
[(ground [[1 31] [3 31] [4 30] [5 31] [6 30]
[7 31] [8 31] [9 30] [10 31] [11 30] [12 31]]) [[?m ?mdays]]]
]
[
(valid-date-parts ?y ?m ?d)
[(>= ?y 1000)]
[(< ?y 10000)]
[(>= ?m 1)]
[(<= ?m 12)]
[(>= ?d 1)]
(month-days ?y ?m ?mdays)
[(<= ?d ?mdays)]
]
[
(parse-date ?date ?y ?m ?d)
[(>= ?date 10000000)]
[(< ?date 100000000)]
[(str ?date) ?yyyymmdd]
[(subs ?yyyymmdd 0 4) ?yyyy]
[(subs ?yyyymmdd 4 6) ?mm]
[(subs ?yyyymmdd 6 8) ?dd]
[(* 1 ?yyyy) ?y]
[(* 1 ?mm) ?m]
[(* 1 ?dd) ?d]
]
;; Base case: year, month, and day are all valid.
[
(date-fix ?y ?m ?d ?date)
(valid-date-parts ?y ?m ?d)
[(* 10000 ?y) ?yp]
[(* 100 ?m) ?mp]
[(+ ?yp ?mp ?d) ?date]
]
;; Day is past the end of the month, but month is OK.
[
(date-fix ?y ?m ?d ?date)
[(>= ?m 1)]
[(<= ?m 12)]
[(>= ?d 1)]
(month-days ?y ?m ?mdays)
[(> ?d ?mdays)]
[(+ ?m 1) ?mp]
[(- ?d ?mdays) ?dp]
(date-fix ?y ?mp ?dp ?date)
]
;; Day is before the start of the month, but month is OK.
;; Also, month is not January.
[
(date-fix ?y ?m ?d ?date)
[(>= ?m 2)]
[(<= ?m 12)]
[(< ?d 1)]
[(- ?m 1) ?mp]
(month-days ?y ?mp ?mpdays)
[(+ ?d ?mpdays) ?dp]
(date-fix ?y ?mp ?dp ?date)
]
;; Day is before the start of the month, but month is OK.
;; Also, month is January.
[
(date-fix ?y ?m ?d ?date)
[(= ?m 1)]
[(< ?d 1)]
[(- ?y 1) ?yp]
[(ground 12) ?mp]
(month-days ?yp ?mp ?mpdays)
[(+ ?d ?mpdays) ?dp]
(date-fix ?yp ?mp ?dp ?date)
]
;; Month is after December.
[
(date-fix ?y ?m ?d ?date)
[(> ?m 12)]
[(+ ?y 1) ?yp]
[(- ?m 12) ?mp]
(date-fix ?yp ?mp ?d ?date)
]
;; Month is before January.
[
(date-fix ?y ?m ?d ?date)
[(< ?m 1)]
[(- ?y 1) ?yp]
[(+ ?m 12) ?mp]
(date-fix ?yp ?mp ?d ?date)
]
[
(date-add ?indate ?years ?months ?days ?outdate)
(parse-date ?indate ?y ?m ?d)
[(+ ?y ?years) ?yp]
[(+ ?m ?months) ?mp]
[(+ ?d ?days) ?dp]
(date-fix ?yp ?mp ?dp ?outdate)
]
]
Examples:
:where
...
(date-add ?refdate 1 0 0 ?oneyearlater)
(date-add ?refdate 0 6 0 ?sixmonthslater)
(date-add ?refdate 0 -6 0 ?sixmonthsearlier)
(date-add ?refdate 0 0 180 ?hundredeightydayslater)
...
However, it seems to be quite slow. Anyone have any ideas to improve performance?
1 Like
If you are open to use custom.js, the best solution would be to implement a kit.
It isn’t slow at all when I try your examples. There has to be something else going on with the rest of your actual query.
1 Like