How to calculate difference in days between two dates

[san-r]

Suppose we have a csv file called test_date.txt as:

name,date1,date2
A,2020-09-20,2020-08-30
B,2020-01-31,2020-01-20

When I tried the command:
mlr --c2p --from test_date.txt put "$date_diff = $date1 - date2"

I got:

name date1      date2      date_diff
A    2020-09-20 2020-08-30 (error)
B    2020-01-31 2020-01-20 (error)

I tried looking at Miller 6 reference, but could not find relevant function.

[san-r]

I got it

mlr --c2p --from test_date.txt ^
put "$date_diff = (strptime($date1, \"%Y-%m-%d\") - strptime($date2, \"%Y-%m-%d\"))/86400"

name date1      date2      date_diff
A    2020-09-20 2020-08-30 21
B    2020-01-31 2020-01-20 11

I wonder if there is a shorter/simpler way.

[johnkerl]

@san-r awesome! :)

This works using strptime since there is no daylight-savings in UTC (nor leap-seconds) and 24 hours is 86400 by fiat.

There isn't currently a shorter/simpler way but we could certainly design a DSL function for calculating day-differences ...

Would that be of interest to you? & if so, what would you want it to look like?

[san-r]

Calculating various kinds of differences between two days is absolutely necessary in my line of work.

My suggestion is that DATEDIF function provided by major spreadsheets should be developed for Miller at the earliest.

• Microsoft Excel usage: https://exceljet.net/excel-functions/excel-datedif-function
• LibreOffice Calc usage: https://help.libreoffice.org/latest/ro/text/scalc/01/func_datedif.html

PS: I've been using R for a few years which has a dedicated package lubridate for making date operations easier. The basic documentation is available at https://cran.r-project.org/web/packages/lubridate/vignettes/lubridate.html and its development/cheatsheets may be seen at https://github.com/tidyverse/lubridate. It covers all the date operations that I've needed until now. But I guess it would be too much to suggest at this time.

[johnkerl]

@san-r nice! :)

Here is a prototype -- basically what you wrote above but wrapped in a UDF:

func datediff(a, b) {
  fmt = "%Y-%m-%d";
  as = strptime(a, fmt);
  bs = strptime(b, fmt);
  ds = bs - as;
  return ds /86400;
}

datediff("2021-03-01", "2022-03-01")
365
datediff("2019-03-01", "2020-03-01")
366

On a not-completely-unrelated note -- the way feature development has been working in Miller is:

• Idea, from here on GitHub or some other contact, or my own idea
• At this point the MIller DSL is powerful enough that most DSL/verb ideas can be prototyped purely in the DSL
• Then I implement it in Miller source code (C before Miller 6, Go as of Miller 6)
• Make an i.j.k release of Miller
• Let it flow through package-distribution channels

Note however that maybe leaving things DSL-only is perhaps sufficient in many cases ...

If so, how to manage DSL-only things though?

• As of Miller 6 there's mlr --load: https://johnkerl.org/miller6/reference-dsl-user-defined-functions/#loading-a-library-of-functions
• Maybe there could be some kind of standard library?
• How to distribute it?
• Maybe we could simply have a stdlib subdirectory of https://github.com/johnkerl/miller ...

What are your thoughts on DSL-only library material?

Anyway, I'll definitely make a source-level datediff function (#708) -- not sure what else from lubridate to fold in. Let's keep chatting about this ...

[san-r]

Thanks for assurance of datediff function in Miller. I'm still studying and experimenting with Miller 6 with my work related big gzipped csv files and so far it's doing great.