Skip to content
Quick links:   Flags   Verbs   Functions   Glossary   Release docs

DSL datetime/timezone functions

Dates/times are not a separate data type; Miller uses ints for seconds since the epoch and strings for formatted date/times. In this page we take a look at what some of the various options are for processing datetimes andd timezones in your data.

See also the section on time-related functions for information auto-generated from Miller's online-help strings.

Epoch seconds

Seconds since the epoch, or Unix Time, is seconds (positive, zero, or negative) since midnight January 1 1970 UTC. This representation has several advantages, and is quite common in the computing world.

Since this is a number in Miller -- 64-bit signed integer or double-precision floating-point -- it can represent dates billions of years into the past or future without worry of overflow. (There is no year-2038 problem here.) Being numbers, epoch-seconds are easy to store in databases, communicate over networks in binary format, etc. Another benefit of epoch-seconds is that they're independent of timezone or daylight-savings time.

One minus is that, being just numbers, they're not particularly human-readable -- hence the to-string and from-string functions described below. Another caveat (not really a minus) is that epoch milliseconds, rather than epoch seconds, are common in some contexts, particulary JavaScript. If you ever (anywhere) see a timestamp for the year 49,000-something -- probably someone is treating epoch-milliseconds as epoch-seconds.

mlr -n put 'end {
  print sec2gmt(1500000000);
  print sec2gmt(1500000000000);
}'
2017-07-14T02:40:00Z
49503-02-10T02:40:00Z

You can get the current system time, as epoch-seconds, using the systime DSL function:

mlr --c2p --from example.csv put '$t = systime()'
color  shape    flag  k  index quantity rate   t
yellow triangle true  1  11    43.6498  9.8870 1634784588.045347
red    square   true  2  15    79.2778  0.0130 1634784588.045385
red    circle   true  3  16    13.8103  2.9010 1634784588.045386
red    square   false 4  48    77.5542  7.4670 1634784588.045393
purple triangle false 5  51    81.2290  8.5910 1634784588.045394
red    square   false 6  64    77.1991  9.5310 1634784588.045417
purple triangle false 7  65    80.1405  5.8240 1634784588.045418
yellow circle   true  8  73    63.9785  4.2370 1634784588.045419
yellow circle   true  9  87    63.5058  8.3350 1634784588.045421
purple square   false 10 91    72.3735  8.2430 1634784588.045422

The systimeint DSL functions is nothing more than a keystroke-saver for int(systime()).

UTC times with standard format

One way to make epoch-seconds human-readable, while maintaining some of their benefits such as being independent of timezone and daylight savings, is to use the ISO8601 format. This was the first (and initially only) human-readable date/time format supported by Miller going all the way back to Miller 1.0.0.

You can get these from epoch-seconds using the sec2gmt DSL function. (Note that the terms UTC and GMT are used interchangeably in Miller.) We also have sec2gmtdate DSL function.

mlr -n put 'end {
  print sec2gmt(0);
  print sec2gmt(1234567890.123);
  print sec2gmt(-1234567890.123);
  print;
  print sec2gmtdate(0);
  print sec2gmtdate(1234567890.123);
  print sec2gmtdate(-1234567890.123);
}'
1970-01-01T00:00:00Z
2009-02-13T23:31:30Z
1930-11-18T00:28:29Z

1970-01-01
2009-02-13
1930-11-18

Local times with standard format; specifying timezones

You can use similar formatting for dates in your preferred timezone, not just UTC/GMT. We have the sec2localtime, sec2localdate, and localtime2sec DSL functions.

You can specify the timezone using any of the following:

  • An environment variable, e.g. export TZ=Asia/Istanbul at your system prompt (set TZ=Asia/Istanbul in Windows).
  • Using the --tz flag. This sets the TZ environment variable, but only internally to the mlr process.
  • Within a DSL expression, you can assign to ENV["TZ"].
  • By supplying an additional argument to any of the functions with local in their names.

Regardless, if you specify an invalid timezone, you'll be clearly notified:

mlr --from example.csv --tz This/Is/A/Typo cat
mlr: unknown time zone This/Is/A/Typo
export TZ=Asia/Istanbul
mlr -n put 'end { print sec2localtime(0) }'
1970-01-01 02:00:00
mlr --tz America/Sao_Paulo -n put 'end { print sec2localtime(0) }'
1969-12-31 21:00:00
mlr -n put 'end {
  ENV["TZ"] = "Asia/Istanbul";
  print sec2localtime(0);
  print sec2localdate(0);
  print localtime2sec("2000-01-02 03:04:05");
  print;
  ENV["TZ"] = "America/Sao_Paulo";
  print sec2localtime(0);
  print sec2localdate(0);
  print localtime2sec("2000-01-02 03:04:05");
}'
1970-01-01 02:00:00
1970-01-01
946775045

1969-12-31 21:00:00
1969-12-31
946789445
mlr -n put 'end {
  print sec2localtime(0, 0, "Asia/Istanbul");
  print sec2localdate(0, "Asia/Istanbul");
  print localtime2sec("2000-01-02 03:04:05", "Asia/Istanbul");
  print;
  print sec2localtime(0, 0, "America/Sao_Paulo");
  print sec2localdate(0, "America/Sao_Paulo");
  print localtime2sec("2000-01-02 03:04:05", "America/Sao_Paulo");
}'
1970-01-01 02:00:00
1970-01-01
946775045

1969-12-31 21:00:00
1969-12-31
946789445

Note that for local times, Miller omits the T and the Z you see in GMT times.

We also have the gmt2localtime and localtime2gmt convenience functions:

mlr -n put 'end {
  ENV["TZ"] = "Asia/Istanbul";
  print gmt2localtime("1970-01-01T00:00:00Z");
  print localtime2gmt("1970-01-01 00:00:00");
}'
1970-01-01 02:00:00
1969-12-31T22:00:00Z
mlr -n put 'end {
  print gmt2localtime("1970-01-01T00:00:00Z", "America/Sao_Paulo");
  print gmt2localtime("1970-01-01T00:00:00Z", "Asia/Istanbul");
  print localtime2gmt("1970-01-01 00:00:00",  "America/Sao_Paulo");
  print localtime2gmt("1970-01-01 00:00:00",  "Asia/Istanbul");
}'
1969-12-31 21:00:00
1970-01-01 02:00:00
1970-01-01T03:00:00Z
1969-12-31T22:00:00Z

GMT and local times with custom formats

The to-string and from-string functions we've seen so far are low-keystroking: with a little bit of typing you can convert datetimes to/from epoch seconds. The minus, however, is flexibility. This is where the strftime, strptime functions come into play.

Notes:

Some examples:

mlr -n put 'end {
  ENV["TZ"] = "Asia/Istanbul";
  print strftime(0, "%Y-%m-%d %H:%M:%S");
  print strftime(0, "%Y-%m-%d %H:%M:%S %Z");
  print strftime(0, "%Y-%m-%d %H:%M:%S %z");
  print strftime(0, "%A, %B %e, %Y");
  print strftime(123456789, "%I:%M %p");
}'
1970-01-01 00:00:00
1970-01-01 00:00:00 UTC
1970-01-01 00:00:00 +0000
Thursday, January  1, 1970
09:33 PM

Unfortunately, names from %A and %B are only available in English, as an artifact of a design choice in the Go time library which Miller (and its strftime / strptime supporting packages as noted above) rely on.

We also have strftimelocal and strptimelocal:

mlr -n put 'end {
  ENV["TZ"] = "America/Anchorage";
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %Z");
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %z");
  print strftime_local(0, "%A, %B %e, %Y");
  print strptime_local("2020-03-01 00:00:00", "%Y-%m-%d %H:%M:%S");
  print;
  ENV["TZ"] = "Asia/Hong_Kong";
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %Z");
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %z");
  print strftime_local(0, "%A, %B %e, %Y");
  print strptime_local("2020-03-01 00:00:00", "%Y-%m-%d %H:%M:%S");
}'
1969-12-31 14:00:00 AHST
1969-12-31 14:00:00 -1000
Wednesday, December 31, 1969
1583053200

1970-01-01 08:00:00 HKT
1970-01-01 08:00:00 +0800
Thursday, January  1, 1970
1582992000
mlr -n put 'end {
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %Z", "America/Anchorage");
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %z", "America/Anchorage");
  print strftime_local(0, "%A, %B %e, %Y",        "America/Anchorage");
  print strptime_local("2020-03-01 00:00:00", "%Y-%m-%d %H:%M:%S", "America/Anchorage");
  print;
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %Z", "Asia/Hong_Kong");
  print strftime_local(0, "%Y-%m-%d %H:%M:%S %z", "Asia/Hong_Kong");
  print strftime_local(0, "%A, %B %e, %Y",        "Asia/Hong_Kong");
  print strptime_local("2020-03-01 00:00:00", "%Y-%m-%d %H:%M:%S", "Asia/Hong_Kong");
}'
1969-12-31 14:00:00 AHST
1969-12-31 14:00:00 -1000
Wednesday, December 31, 1969
1583053200

1970-01-01 08:00:00 HKT
1970-01-01 08:00:00 +0800
Thursday, January  1, 1970
1582992000

Relative times

You can get the seconds since the Miller process start using uptime:

color  shape    flag  k  index quantity rate   u
yellow triangle true  1  11    43.6498  9.8870 0.0011110305786132812
red    square   true  2  15    79.2778  0.0130 0.0011241436004638672
red    circle   true  3  16    13.8103  2.9010 0.0011250972747802734
red    square   false 4  48    77.5542  7.4670 0.0011301040649414062
purple triangle false 5  51    81.2290  8.5910 0.0011301040649414062
red    square   false 6  64    77.1991  9.5310 0.002481222152709961
purple triangle false 7  65    80.1405  5.8240 0.0024831295013427734
yellow circle   true  8  73    63.9785  4.2370 0.0024831295013427734
yellow circle   true  9  87    63.5058  8.3350 0.0024852752685546875
purple square   false 10 91    72.3735  8.2430 0.002485990524291992

Time-differences can be done in seconds, of course; you can also use the following if you like:

mlr -F | grep hms
dhms2fsec  (class=time #args=1) Recovers floating-point seconds as in dhms2fsec("5d18h53m20.250000s") = 500000.250000
dhms2sec  (class=time #args=1) Recovers integer seconds as in dhms2sec("5d18h53m20s") = 500000
fsec2dhms  (class=time #args=1) Formats floating-point seconds as in fsec2dhms(500000.25) = "5d18h53m20.250000s"
fsec2hms  (class=time #args=1) Formats floating-point seconds as in fsec2hms(5000.25) = "01:23:20.250000"
hms2fsec  (class=time #args=1) Recovers floating-point seconds as in hms2fsec("01:23:20.250000") = 5000.250000
hms2sec  (class=time #args=1) Recovers integer seconds as in hms2sec("01:23:20") = 5000
sec2dhms  (class=time #args=1) Formats integer seconds as in sec2dhms(500000) = "5d18h53m20s"
sec2hms  (class=time #args=1) Formats integer seconds as in sec2hms(5000) = "01:23:20"

References

Back to top