Date & time developer: MQL recipes

Recipes

Support ticket SLA in business days
One row of a date dimension table
Time zones, ISO 8601 and epoch for an API
Batch window from a legacy file header
Run log header and execution timer
Monthly billing dates and proration of an upgrade
French public holidays and a delivery date in working days
Weekly sign-up cohorts and day-14 retention
Is the shop open now, and when does it open next
GDPR retention: keep, anonymise or delete customers

executed on MentDB

Support ticket SLA in business days

A ticket opened on Friday 27 November 2026 at 18:40 must be solved within 3 business days. The script computes the deadline, skips the weekend, counts the business days used and checks whether the closing timestamp is within the SLA.

Pipeline

01 · deadlineDeadline + 3 business daysdate add_business_days→02 · weekendWeekend and next mondaydate next_day→03 · countBusiness days useddate nb_business_days→04 · checkBefore / after the limitdate is_before→05 · elapsedReadable durationdate duration

Script

admin

#1. Ticket opened on a Friday evening, SLA of 3 business days;
-> "[opened]" "2026-11-27 18:40:00";
-> "[closed]" "2026-12-02 09:15:30";
-> "[day]" (string substring [opened] 0 10);
-> "[deadline]" (date add_business_days [day] 3);
#2. Calendar facts for the support team;
-> "[sat]" (date add [day] "DAY" 1);
-> "[used]" (date nb_business_days [day] (string substring [closed] 0 10));
#3. SLA checks on the closing timestamp;
-> "[limit]" (concat [deadline] " 23:59:59");
concat "{\"opened\": \"" (date dayname [day]) " " [day] "\", \"next_day_is_weekend\": " (date is_weekend [sat]) ", \"next_monday\": \"" (date next_day [day] "monday") "\", \"deadline\": \"" [deadline] "\", \"business_days_used\": " [used] ", \"in_sla\": " (date is_before [closed] [limit]) ", \"late\": " (date is_after [closed] [limit]) ", \"elapsed\": \"" (date duration [opened] [closed]) "\", \"closed_in_december\": " (date between [closed] "2026-12-01" "2026-12-31 23:59:59") ", \"closed_hour\": \"" (date trunc [closed] "HOUR") "\"}"

Result

mentdb

{"opened": "vendredi 2026-11-27", "next_day_is_weekend": 1, "next_monday": "2026-11-30", "deadline": "2026-12-02", "business_days_used": 3, "in_sla": 1, "late": 0, "elapsed": "4d 14h 35min 30s", "closed_in_december": 1, "closed_hour": "2026-12-02 09:00:00"}

Go further

Public holidays are not known by date add_business_days: keep them in a JSON array and push the deadline with date between
Group the tickets by date week_of_year to chart the SLA rate per week

executed on MentDB

One row of a date dimension table

A data warehouse needs a calendar table with one row per day. For 17 February 2028 the script builds the JSON row: key yyyyMMdd, quarter, ISO week, day of year, week and month bounds, leap year and days to the year end.

Pipeline

01 · keyDate key yyyyMMdddate format→02 · partsYear, quarter, ISO weekdate week_of_year→03 · boundsWeek and month boundsdate first_day_of_week→04 · yearLeap year and days leftdate is_leap_year→05 · rowJSON rowjson iobject

Script

admin

#1. One row of the date dimension of a data warehouse;
-> "[d]" "2028-02-17";
json load "rx_dim_date" "{}";
json iobject "rx_dim_date" / date_key (date format [d] "yyyy-MM-dd" "yyyyMMdd") INT;
json iobject "rx_dim_date" / year (date year [d]) INT;
json iobject "rx_dim_date" / quarter (date quarter [d]) INT;
json iobject "rx_dim_date" / month (date month [d]) INT;
json iobject "rx_dim_date" / month_name (date monthname [d]) STR;
json iobject "rx_dim_date" / day_of_month (date day_of_month [d]) INT;
json iobject "rx_dim_date" / day_of_year (date day_of_year [d]) INT;
json iobject "rx_dim_date" / day_of_week (date day_of_week [d]) INT;
json iobject "rx_dim_date" / iso_week (date week_of_year [d]) INT;
#2. Period bounds used by the reports;
json iobject "rx_dim_date" / week (concat (date first_day_of_week [d]) " / " (date last_day_of_week [d])) STR;
json iobject "rx_dim_date" / month_bounds (concat (date first_day_of_month [d]) " / " (date last_day_of_month [d])) STR;
json iobject "rx_dim_date" / days_in_month (date days_in_month [d]) INT;
json iobject "rx_dim_date" / leap_year (date is_leap_year [d]) INT;
json iobject "rx_dim_date" / days_since_new_year (date nb_day (date first_day_of_year [d]) [d]) INT;
json iobject "rx_dim_date" / days_to_year_end (date nb_day [d] (date last_day_of_year [d])) INT;
-> "[row]" (json doc "rx_dim_date");
json unload "rx_dim_date";
[row]

Result

mentdb

{
  "days_to_year_end": 318,
  "month_name": "février",
  "month_bounds": "2028-02-01 / 2028-02-29",
  "week": "2028-02-14 / 2028-02-20",
  "year": 2028,
  "day_of_year": 48,
  "iso_week": 7,
  "day_of_month": 17,
  "date_key": 20280217,
  "leap_year": 1,
  "month": 2,
  "days_since_new_year": 47,
  "days_in_month": 29,
  "quarter": 1,
  "day_of_week": 4
}

Go further

Loop from date first_day_of_year to date last_day_of_year with date add to generate the 366 rows
date monthname and date dayname follow the server locale (French here): keep a JSON of English names if you need them

executed on MentDB

Time zones, ISO 8601 and epoch for an API

A meeting is set at 09:00 Paris time on 29 March 2026, the day Europe switches to summer time. The script converts it to the server zone and to New York, then gives the UTC, ISO 8601, epoch seconds and milliseconds forms and reads back an ISO event received with Z.

Pipeline

01 · convertParis to server zonedate convert_tz→02 · isoISO 8601 with offsetdate to_iso→03 · epochEpoch seconds and msdate to_epoch→04 · parseISO Z to localdate from_iso→05 · utcUTC round tripdate to_utc

Script

admin

#1. A meeting at 09:00 in Paris on the day of the summer time change;
-> "[paris]" "2026-03-29 09:00:00";
-> "[local]" (date convert_tz [paris] "Europe/Paris" (date timezone));
#2. ISO 8601, UTC and epoch for the API;
-> "[iso]" (date to_iso [local]);
-> "[epoch]" (date to_epoch [local]);
-> "[ms]" (date ts_to_long [local]);
#3. An event received from a partner in ISO with Z;
-> "[event]" (date from_iso "2026-03-29T06:30:00Z");
concat "{\"server_tz\": \"" (date timezone) "\", \"local\": \"" [local] "\", \"new_york\": \"" (date convert_tz [paris] "Europe/Paris" "America/New_York") "\", \"utc\": \"" (date to_utc [local]) "\", \"iso\": \"" [iso] "\", \"epoch\": " [epoch] ", \"back_from_epoch\": \"" (date from_epoch [epoch]) "\", \"epoch_ms\": " [ms] ", \"ms_to_ts\": \"" (date long_to_ts [ms]) "\", \"day_ms\": " (date dt_to_long "2026-03-29") ", \"ms_to_day\": \"" (date long_to_dt [ms]) "\", \"event_local\": \"" [event] "\", \"event_utc_back\": \"" (date from_utc (date to_utc [event])) "\"}"

Result

mentdb

{"server_tz": "Indian/Reunion", "local": "2026-03-29 11:00:00", "new_york": "2026-03-29 03:00:00", "utc": "2026-03-29 07:00:00", "iso": "2026-03-29T11:00:00+04:00", "epoch": 1774767600, "back_from_epoch": "2026-03-29 11:00:00", "epoch_ms": 1774767600000, "ms_to_ts": "2026-03-29 11:00:00", "day_ms": 1774728000000, "ms_to_day": "2026-03-29", "event_local": "2026-03-29 10:30:00", "event_utc_back": "2026-03-29 10:30:00"}

Go further

Store timestamps with date to_utc and show them with date from_utc to avoid DST surprises
date ts_to_long gives JavaScript-ready milliseconds for a chart axis

executed on MentDB

Batch window from a legacy file header

A legacy file starts with a run date in dd/MM/yyyy and a start time in HHmm. The script validates and converts them, computes the end of the 6h30 batch window, the previous run and the purge limit three months back, and shows that impossible dates are rejected.

Pipeline

01 · validateCheck dd/MM/yyyydate is_valid_date→02 · convertTo yyyy-MM-dd HH:mm:ssdate format→03 · window+6h30 windowdate addt→04 · splitHour, minute, seconddate hour→05 · purgeThree months backdate diff

Script

admin

#1. Header of a legacy file: run date dd/MM/yyyy and start time HHmm;
-> "[h_date]" "05/11/2026";
-> "[h_time]" "2330";
-> "[ok_date]" (date is_valid_date [h_date] "dd/MM/yyyy");
-> "[ts]" (date format (concat [h_date] " " [h_time]) "dd/MM/yyyy HHmm" "yyyy-MM-dd HH:mm:ss");
-> "[ok_ts]" (date is_valid_timestamp [ts] "yyyy-MM-dd HH:mm:ss");
#2. Batch window: starts at ts, lasts 6h30, retention of 3 months;
-> "[end]" (date dateaddt (date addt [ts] "HOUR" 6) "MIN" 30);
-> "[purge]" (date datediff (date diff (string left [ts] 10) "MONTH" 2) "MONTH" 1);
#3. Previous run and checks of a wrong header;
-> "[prev]" (date datedifft (date difft [ts] "DAY" 1) "HOUR" 0);
concat "{\"header_ok\": " [ok_date] ", \"start\": \"" [ts] "\", \"timestamp_ok\": " [ok_ts] ", \"end\": \"" [end] "\", \"end_time\": \"" (date time [end]) "\", \"end_hms\": \"" (date hour (date time [end])) "h" (date minute (date time [end])) "m" (date seconde (date time [end])) "s\", \"next_day\": \"" (date dateadd (string left [ts] 10) "DAY" 1) "\", \"previous_run\": \"" [prev] "\", \"purge_before\": \"" [purge] "\", \"bad_date_31_04\": " (date is_valid_date "2026-04-31") ", \"bad_time_25h\": " (date is_valid_time "25:10:00") ", \"bad_ts\": " (date is_valid_timestamp "2026-02-30 10:00:00") "}"

Result

mentdb

{"header_ok": 1, "start": "2026-11-05 23:30:00", "timestamp_ok": 1, "end": "2026-11-06 06:00:00", "end_time": "06:00:00", "end_hms": "06h00m00s", "next_day": "2026-11-06", "previous_run": "2026-11-04 23:30:00", "purge_before": "2026-08-05", "bad_date_31_04": 0, "bad_time_25h": 0, "bad_ts": 0}

Go further

Reject the file early when date is_valid_timestamp returns 0 and log the header
The field of date addt is SEC|MIN|HOUR|DAY|MONTH|YEAR

executed on MentDB

Run log header and execution timer

A nightly script writes a log file named after the start time and measures how long its work takes. Here the work counts the weekdays of 2026 day by day and checks the total against date nb_business_days; the header lists the server clock in all its forms.

Pipeline

01 · startStart in ms and nsdate current_ms→02 · workLoop over the daysdate is_weekend→03 · stopElapsed ms and µsdate current_ns→04 · headerLog name and clockdate systimestamp_min→05 · utcUTC and epochdate systimestamp_utc

Script

admin

#1. Start a timer in ms and in ns;
-> "[t0]" (date current_ms);
-> "[n0]" (date current_ns);
#2. The work to measure: count the weekdays of 2026 day by day;
-> "[n]" 0;
-> "[d]" "2026-01-01";
for (-> "[i]" 1) (<= [i] 365) (++ "[i]") {
  if (== (date is_weekend [d]) 0) {++ "[n]";};
  -> "[d]" (date add [d] "DAY" 1);
};
-> "[ms]" (- (date current_ms) [t0]);
-> "[us]" (math round (/ (- (date current_ns) [n0]) 1000) 0);
#3. Log header with the server clock in all its forms;
-> "[log]" (concat "run_" (date systimestamp_min) ".log");
concat "{\"log_file\": \"" [log] "\", \"date\": \"" (date curdate) "|" (date current_date) "|" (date sysdate) "\", \"time\": \"" (date curtime) "|" (date current_time) "|" (date systime) "\", \"ts\": \"" (date now) "|" (date systimestamp) "|" (date curtimestamp) "|" (date current_timestamp) "\", \"full\": \"" (date full_systimestamp) "\", \"utc\": \"" (date systimestamp_utc) "\", \"epoch\": " (date epoch) ", \"operator_age\": " (date age "1980-06-18") ", \"weekdays_2026\": " [n] ", \"check\": " (date nb_business_days "2026-01-01" "2027-01-01") ", \"elapsed_ms\": " [ms] ", \"elapsed_us\": " [us] "}"

Result

mentdb

{"log_file": "run_20261101162325.log", "date": "2026-11-01|2026-11-01|2026-11-01", "time": "16:23:25|16:23:25|16:23:25", "ts": "2026-11-01 16:23:25|2026-11-01 16:23:25|2026-11-01 16:23:25|2026-11-01 16:23:25", "full": "2026-11-01 16:23:25.843+0400", "utc": "2026-11-01 12:23:25", "epoch": 1793535805, "operator_age": 46, "weekdays_2026": 261, "check": 261, "elapsed_ms": 5, "elapsed_us": 4782}

Go further

Write the header with log trace and keep the elapsed time in a node to follow trends
date full_systimestamp keeps the milliseconds and the offset for log correlation

executed on MentDB

Monthly billing dates and proration of an upgrade

A subscription started on January 31. The script computes the next billing dates from the anchor day, falling back to the last day of shorter months, shows why chaining one-month adds drifts, and credits the unused days of a period when the customer upgrades on March 10.

Pipeline

01 · anchorAnchor day of the subscriptiondate day_of_month→02 · monthFirst day of each next monthdate first_day_of_month→03 · clampDays in that monthdate days_in_month→04 · prorateUnused days of the perioddate nb_day→05 · leapFebruary of a leap yeardate is_leap_year

Script

admin

#1. A subscription started on January 31 at 29.90 per month;
-> "[start]" "2027-01-31";
-> "[price]" 29.90;
-> "[anchor]" (date day_of_month [start]);
-> "[out]" "";
-> "[days_en]" "Mon,Tue,Wed,Thu,Fri,Sat,Sun";
#2. Next billing dates: same day each month, or the last day of a shorter month;
for (-> "[m]" 1) (<= [m] 5) (++ "[m]") {
	-> "[first]" (date first_day_of_month (date add (date first_day_of_month [start]) "MONTH" [m]));
	-> "[dim]" (date days_in_month [first]);
	-> "[day]" (if (> [anchor] [dim]) {[dim]} {[anchor]});
	-> "[bill]" (date add [first] "DAY" (- [day] 1));
	-> "[out]" (concat [out] "invoice " [m] ": " [bill] " " (atom get [days_en] (date day_of_week [bill]) ",") (if (== [day] [anchor]) {""} {" (end of month)"}) [_n_]);
};
#3. Upgrade on March 10: credit the unused days of the current period;
-> "[period_start]" "2027-02-28";
-> "[period_end]" "2027-03-31";
-> "[change]" "2027-03-10";
-> "[days]" (date nb_day [period_start] [period_end]);
-> "[left]" (date nb_day [change] [period_end]);
-> "[credit]" (math round (* [price] (/ [left] [days])) 2);
#4. Adding one month to the previous bill drifts: Feb 28 then Mar 28;
concat [out] "chained adds from " [start] ": " (date add [start] "MONTH" 1) " then " (date add (date add [start] "MONTH" 1) "MONTH" 1) ", so always count from the anchor day" [_n_] "period of " [days] " days, " [left] " unused after " [change] ": credit " [credit] " EUR" [_n_] "2028 is leap: " (date is_leap_year "2028-01-01") ", February 2028 bill: " (date add "2028-02-01" "DAY" (- (date days_in_month "2028-02-01") 1));

Result

mentdb

invoice 1: 2027-02-28 Sun (end of month)
invoice 2: 2027-03-31 Wed
invoice 3: 2027-04-30 Fri (end of month)
invoice 4: 2027-05-31 Mon
invoice 5: 2027-06-30 Wed (end of month)
chained adds from 2027-01-31: 2027-02-28 then 2027-03-28, so always count from the anchor day
period of 31 days, 21 unused after 2027-03-10: credit 20.25 EUR
2028 is leap: 1, February 2028 bill: 2028-02-29

Go further

Store the anchor day with the subscription, never the last billing date, or the bills drift to the 28th.
Create the invoices with a job on the first day of each month that selects the subscriptions due.

executed on MentDB

French public holidays and a delivery date in working days

Compute Easter Sunday with the Meeus algorithm, derive Easter Monday, Ascension and Whit Monday, add the fixed French holidays, then compute a delivery date in 5 working days that skips weekends and holidays, compared with date add_business_days that only skips weekends.

Pipeline

01 · easterInteger divisions of the algorithmmath trunc→02 · calcFormulas with variablesmath calc→03 · derivedEaster + 1, + 39, + 50 daysdate add→04 · skipWeekends and holidaysdate is_weekend→05 · compareWeekends onlydate add_business_days

Script

admin

#1. Easter Sunday of a year with the Meeus algorithm, integer divisions with math trunc;
-> "[y]" 2027;
-> "[a]" (math mod [y] 19);
-> "[b]" (math trunc (/ [y] 100));
-> "[c]" (math mod [y] 100);
-> "[d4]" (math trunc (/ [b] 4));
-> "[e]" (math mod [b] 4);
-> "[f]" (math trunc (/ (+ [b] 8) 25));
-> "[g]" (math trunc (/ (math calc "[b] - [f] + 1") 3));
-> "[h]" (math mod (math calc "19*[a] + [b] - [d4] - [g] + 15") 30);
-> "[i4]" (math trunc (/ [c] 4));
-> "[k]" (math mod [c] 4);
-> "[l]" (math mod (math calc "32 + 2*[e] + 2*[i4] - [h] - [k]") 7);
-> "[m]" (math trunc (/ (math calc "[a] + 11*[h] + 22*[l]") 451));
-> "[n]" (math calc "[h] + [l] - 7*[m] + 114");
-> "[month]" (math trunc (/ [n] 31));
-> "[day]" (+ (math mod [n] 31) 1);
-> "[easter]" (concat [y] "-" (string lpad [month] "0" 2) "-" (string lpad [day] "0" 2));
#2. The 11 French public holidays: fixed dates and dates after Easter;
-> "[holidays]" (concat [y] "-01-01," (date add [easter] "DAY" 1) "," [y] "-05-01," [y] "-05-08," (date add [easter] "DAY" 39) "," (date add [easter] "DAY" 50) "," [y] "-07-14," [y] "-08-15," [y] "-11-01," [y] "-11-11," [y] "-12-25");
#3. Delivery in 5 working days from an order of May 4: skip weekends and holidays;
-> "[d]" "2027-05-04";
-> "[left]" 5;
-> "[skipped]" "";
while (> [left] 0) {
	-> "[d]" (date add [d] "DAY" 1);
	if (== (date is_weekend [d]) 1) {continue;};
	if (> (atom find [holidays] [d] ",") 0) {-> "[skipped]" (concat [skipped] [d] " "); continue;};
	-- "[left]";
};
#4. Holidays falling on a weekday this year;
-> "[weekday]" 0;
for (-> "[i]" 1) (<= [i] (atom size [holidays] ",")) (++ "[i]") {
	if (== (date is_weekend (atom get [holidays] [i] ",")) 0) {++ "[weekday]";};
};
concat "Easter " [y] ": " [easter] [_n_] "holidays: " (string replace [holidays] "," ", ") [_n_] [weekday] " of 11 fall on a weekday" [_n_] "order 2027-05-04 + 5 working days = " [d] ", holidays skipped: " (string trim [skipped]) [_n_] "without the holidays: " (date add_business_days "2027-05-04" 5);

Result

mentdb

Easter 2027: 2027-03-28
holidays: 2027-01-01, 2027-03-29, 2027-05-01, 2027-05-08, 2027-05-06, 2027-05-17, 2027-07-14, 2027-08-15, 2027-11-01, 2027-11-11, 2027-12-25
7 of 11 fall on a weekday
order 2027-05-04 + 5 working days = 2027-05-12, holidays skipped: 2027-05-06
without the holidays: 2027-05-11

Go further

Keep the holiday list per year in a node or a parameter so every service uses the same calendar.
Alsace-Moselle adds Good Friday (Easter - 2) and December 26: add them when the site is there.

executed on MentDB

Weekly sign-up cohorts and day-14 retention

Group the users of an app by the ISO week of their sign-up, count each cohort and the users still active 14 days later, then describe the cohorts with a frequency table and the active life with the median and the harmonic mean.

Pipeline

01 · weekISO week of the sign-update week_of_year→02 · mondayMonday of that weekdate first_day_of_week→03 · lifeDays of activitydate nb_day→04 · freqSize of each cohortstat frequency→05 · meanTypical lifestat harm_mean

Script

admin

#1. Sign-up date and last activity of 10 users of a mobile app;
-> "[users]" "2026-10-05>2026-10-29,2026-10-06>2026-10-07,2026-10-08>2026-10-30,2026-10-11>2026-10-12,2026-10-12>2026-10-31,2026-10-14>2026-10-28,2026-10-15>2026-10-16,2026-10-19>2026-10-20,2026-10-20>2026-10-30,2026-10-22>2026-10-23";
json load "rx_p4l_coh" "{}";
-> "[weeks]" "";
-> "[lifetimes]" "";
for (-> "[i]" 1) (<= [i] (atom size [users] ",")) (++ "[i]") {
	-> "[u]" (atom get [users] [i] ",");
	-> "[signup]" (atom get [u] 1 ">");
	-> "[last]" (atom get [u] 2 ">");
	#2. Cohort = ISO week of the sign-up, active life in days;
	-> "[w]" (date week_of_year [signup]);
	-> "[life]" (date nb_day [signup] [last]);
	-> "[weeks]" (concat [weeks] (if (> [i] 1) {","} {""}) [w]);
	-> "[lifetimes]" (concat [lifetimes] (if (> [i] 1) {","} {""}) [life]);
	-> "[key]" (concat "W" [w] "_" (date first_day_of_week [signup]));
	if (is null (json select "rx_p4l_coh" (concat "/" [key]))) {json iobject "rx_p4l_coh" "/" [key] "{\"users\": 0, \"retained_d14\": 0}" OBJ;};
	json uobject "rx_p4l_coh" (concat "/" [key]) "users" (+ (json select "rx_p4l_coh" (concat "/" [key] "/users")) 1) INT;
	#3. Retained = still active 14 days or more after the sign-up;
	if (>= [life] 14) {json uobject "rx_p4l_coh" (concat "/" [key]) "retained_d14" (+ (json select "rx_p4l_coh" (concat "/" [key] "/retained_d14")) 1) INT;};
};
#4. Size of the cohorts and typical life;
-> "[freq]" (stat frequency (concat "[" [weeks] "]"));
-> "[out]" (concat (string replace (string replace (json doc "rx_p4l_coh") [_n_] "") "  " "") [_n_] "cohort sizes:" [_n_] (string replace (string replace (string replace [freq] [_n_] "") "  " "") "},{" (concat "}," [_n_] "{")) [_n_] "median life " (stat median (concat "[" [lifetimes] "]")) " days, harmonic mean " (math round (stat harm_mean (concat "[" [lifetimes] "]")) 1));
json unload "rx_p4l_coh";
[out];

Result

mentdb

{"W41_2026-10-05": {"retained_d14": 2,"users": 4},"W42_2026-10-12": {"retained_d14": 2,"users": 3},"W43_2026-10-19": {"retained_d14": 0,"users": 3}}
cohort sizes:
[{"value": 41, "count": 4, "frequency": 0.4, "cumulative_count": 4, "cumulative_frequency": 0.4},
{"value": 42, "count": 3, "frequency": 0.3, "cumulative_count": 7, "cumulative_frequency": 0.7},
{"value": 43, "count": 3, "frequency": 0.3, "cumulative_count": 10, "cumulative_frequency": 1}]
median life 5.5 days, harmonic mean 1.9

Go further

Plot the retention of each cohort with plot bar to compare the weeks.
Read the sign-ups with sql to json from the users table instead of a list.

executed on MentDB

Is the shop open now, and when does it open next

From the opening hours of a shop by day of week (split days, closed days) and a list of holidays, answer for several moments of Christmas week whether the shop is open, when it closes, or when it opens next and in how long.

Pipeline

01 · weekdayHours of the day of weekdate day_of_week→02 · slotInside an opening slotdate between→03 · nextFirst opening in the futuredate is_after→04 · waitTime to waitdate duration→05 · closeClosing timedate time

Script

admin

#1. Opening hours of a shop by day of week, 1 = Monday, and the closing days;
json load "rx_p4l_hours" "{\"1\": \"\", \"2\": \"09:00-12:30,14:00-19:00\", \"3\": \"09:00-12:30,14:00-19:00\", \"4\": \"09:00-12:30,14:00-19:00\", \"5\": \"09:00-19:00\", \"6\": \"09:30-18:00\", \"7\": \"\"}";
-> "[closed]" "2026-12-25,2027-01-01";
-> "[answers]" "";
-> "[asks]" "2026-12-24 12:45:00,2026-12-24 17:10:00,2026-12-24 19:30:00,2026-12-27 10:00:00";
for (-> "[q]" 1) (<= [q] (atom size [asks] ",")) (++ "[q]") {
	-> "[now]" (atom get [asks] [q] ",");
	-> "[open]" 0;
	-> "[next]" "";
	#2. Look at today, then the next days, until an opening is found;
	for (-> "[k]" 0) (and (<= [k] 7) (is empty [next])) (++ "[k]") {
		-> "[day]" (date add (string left [now] 10) "DAY" [k]);
		if (== (atom find [closed] [day] ",") 0) {
			-> "[slots]" (json select "rx_p4l_hours" (concat "/" (date day_of_week [day])));
			for (-> "[s]" 1) (and (not (is empty [slots])) (<= [s] (atom size [slots] ","))) (++ "[s]") {
				-> "[from]" (concat [day] " " (atom get (atom get [slots] [s] ",") 1 "-") ":00");
				-> "[to]" (concat [day] " " (atom get (atom get [slots] [s] ",") 2 "-") ":00");
				#3. Open now, or the first opening still in the future;
				if (== [k] 0) {if (date between [now] [from] [to]) {-> "[open]" 1; -> "[next]" (concat "closes at " (date time [to]) " in " (date duration [now] [to]));};};
				if (and (is empty [next]) (date is_after [from] [now])) {-> "[next]" (concat "opens " [from] ", in " (date duration [now] [from]));};
			};
		};
	};
	-> "[answers]" (concat [answers] [now] " -> " (if [open] {"OPEN, "} {"closed, "}) [next] [_n_]);
};
json unload "rx_p4l_hours";
[answers];

Result

mentdb

2026-12-24 12:45:00 -> closed, opens 2026-12-24 14:00:00, in 1h 15min
2026-12-24 17:10:00 -> OPEN, closes at 19:00:00 in 1h 50min
2026-12-24 19:30:00 -> closed, opens 2026-12-26 09:30:00, in 1d 14h
2026-12-27 10:00:00 -> closed, opens 2026-12-29 09:00:00, in 1d 23h

Go further

Expose it as a GET service for the website banner: open, closes at, opens next.
Convert [now] from the visitor time zone with date convert_tz first.

executed on MentDB

GDPR retention: keep, anonymise or delete customers

A quarterly job applies the retention rules of a CRM: personal data kept 3 years after the last contact, invoices kept 10 years. Each record gets KEEP, ANONYMISE (invoices still needed) or DELETE, with its deadline in days, and the next quarterly run date.

Pipeline

01 · deadlineContact + 3 years, invoice + 10 yearsdate add→02 · compareDeadline passeddate is_after→03 · rulePick the actioncase→04 · countActions by kindatom count→05 · nextNext quarterdate quarter

Script

admin

#1. The day of the run and the customer records: id, type, last contact, last invoice;
-> "[today]" "2026-11-01";
json load "rx_p4l_crm" "[{\"id\": \"C-101\", \"type\": \"prospect\", \"contact\": \"2023-06-14\", \"invoice\": \"\"}, {\"id\": \"C-102\", \"type\": \"prospect\", \"contact\": \"2024-12-02\", \"invoice\": \"\"}, {\"id\": \"C-103\", \"type\": \"customer\", \"contact\": \"2023-09-30\", \"invoice\": \"2016-10-15\"}, {\"id\": \"C-104\", \"type\": \"customer\", \"contact\": \"2025-01-20\", \"invoice\": \"2025-01-20\"}, {\"id\": \"C-105\", \"type\": \"customer\", \"contact\": \"2021-03-08\", \"invoice\": \"2021-03-08\"}]";
-> "[report]" "";
-> "[actions]" "";
#2. Rules: personal data 3 years after the last contact, invoices 10 years;
for (-> "[i]" 0) (< [i] (json count "rx_p4l_crm" "/")) (++ "[i]") {
	-> "[id]" (json select "rx_p4l_crm" (concat "/[" [i] "]/id"));
	-> "[contact]" (json select "rx_p4l_crm" (concat "/[" [i] "]/contact"));
	-> "[invoice]" (json select "rx_p4l_crm" (concat "/[" [i] "]/invoice"));
	-> "[pii_end]" (date add [contact] "YEAR" 3);
	-> "[inv_end]" (if (is empty [invoice]) {""} {date add [invoice] "YEAR" 10});
	#3. Decide the action on the day of the run;
	-> "[action]" (case
		(date is_after [pii_end] [today]) {"keep"}
		(is empty [invoice]) {"delete"}
		(date is_after [inv_end] [today]) {"anonymise"}
		{"delete"}
	);
	-> "[actions]" (concat [actions] (if (> [i] 0) {","} {""}) [action]);
	-> "[report]" (concat [report] [id] " " (string rpad (json select "rx_p4l_crm" (concat "/[" [i] "]/type")) " " 9) "contact +3y " [pii_end] " (" (date nb_day [today] [pii_end]) " d)" (if (is empty [inv_end]) {""} {concat ", invoice +10y " [inv_end]}) " -> " (string upper [action]) [_n_]);
};
json unload "rx_p4l_crm";
#4. Summary and date of the next quarterly run;
-> "[next_run]" (date first_day_of_month (date add [today] "MONTH" (- 3 (math mod (- (date month [today]) 1) 3))));
concat [report] "keep " (atom count [actions] "keep" ",") ", anonymise " (atom count [actions] "anonymise" ",") ", delete " (atom count [actions] "delete" ",") [_n_] "this run is in Q" (date quarter [today]) ", next quarterly run on " [next_run] " (Q" (date quarter [next_run]) ")";

Result

mentdb

C-101 prospect contact +3y 2026-06-14 (-140 d) -> DELETE
C-102 prospect contact +3y 2027-12-02 (396 d) -> KEEP
C-103 customer contact +3y 2026-09-30 (-32 d), invoice +10y 2026-10-15 -> DELETE
C-104 customer contact +3y 2028-01-20 (445 d), invoice +10y 2035-01-20 -> KEEP
C-105 customer contact +3y 2024-03-08 (-968 d), invoice +10y 2031-03-08 -> ANONYMISE
keep 2, anonymise 1, delete 2
this run is in Q4, next quarterly run on 2027-01-01 (Q1)

Go further

Anonymise with string sha_256 of the e-mail so duplicates can still be detected without the data.
Write each decision with log write to prove the purge to the data protection officer.