Mirrors/miller
1
0
Fork 0
mirror of https://github.com/johnkerl/miller.git synced 2026-07-20 18:10:07 +00:00
Code Issues Projects Releases Wiki Activity
miller/docs6/docs/_build/html/dates-and-times.html

161 lines
No EOL
8.1 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Dates and times &#8212; Miller 6.0.0-alpha documentation</title>
<link rel="stylesheet" href="_static/scrolls.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="_static/print.css" type="text/css" />
<script id="documentation_options" data-url_root="./" src="_static/documentation_options.js"></script>
<script src="_static/jquery.js"></script>
<script src="_static/underscore.js"></script>
<script src="_static/doctools.js"></script>
<script src="_static/language_data.js"></script>
<script src="_static/theme_extras.js"></script>
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="next" title="Then-chaining" href="then-chaining.html" />
<link rel="prev" title="Special symbols and formatting" href="special-symbols-and-formatting.html" />
</head><body>
<div id="content">
<div class="header">
<h1 class="heading"><a href="index.html"
title="back to the documentation overview"><span>Dates and times</span></a></h1>
</div>
<div class="relnav" role="navigation" aria-label="related navigation">
<a href="special-symbols-and-formatting.html">&laquo; Special symbols and formatting</a> |
<a href="#">Dates and times</a>
| <a href="then-chaining.html">Then-chaining &raquo;</a>
</div>
<div id="contentwrapper">
<div id="toc" role="navigation" aria-label="table of contents navigation">
<h3>Table of Contents</h3>
<ul>
<li><a class="reference internal" href="#">Dates and times</a><ul>
<li><a class="reference internal" href="#how-can-i-filter-by-date">How can I filter by date?</a></li>
<li><a class="reference internal" href="#finding-missing-dates">Finding missing dates</a></li>
</ul>
</li>
</ul>
</div>
<div role="main">
<div class="section" id="dates-and-times">
<h1>Dates and times<a class="headerlink" href="#dates-and-times" title="Permalink to this headline"></a></h1>
<div class="section" id="how-can-i-filter-by-date">
<h2>How can I filter by date?<a class="headerlink" href="#how-can-i-filter-by-date" title="Permalink to this headline"></a></h2>
<p>Given input like</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> cat dates.csv
</span> date,event
2018-02-03,initialization
2018-03-07,discovery
2018-02-03,allocation
</pre></div>
</div>
<p>we can use <code class="docutils literal notranslate"><span class="pre">strptime</span></code> to parse the date field into seconds-since-epoch and then do numeric comparisons. Simply match your input dataset’s date-formatting to the <a class="reference internal" href="reference-dsl-builtin-functions.html#reference-dsl-strptime"><span class="std std-ref">strptime</span></a> format-string. For example:</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --csv filter &#39;
</span><span class="hll"> strptime($date, &quot;%Y-%m-%d&quot;) &gt; strptime(&quot;2018-03-03&quot;, &quot;%Y-%m-%d&quot;)
</span><span class="hll"> &#39; dates.csv
</span> date,event
2018-03-07,discovery
</pre></div>
</div>
<p>Caveat: localtime-handling in timezones with DST is still a work in progress; see <a class="reference external" href="https://github.com/johnkerl/miller/issues/170">https://github.com/johnkerl/miller/issues/170</a>. See also <a class="reference external" href="https://github.com/johnkerl/miller/issues/208">https://github.com/johnkerl/miller/issues/208</a> – thanks &#64;aborruso!</p>
</div>
<div class="section" id="finding-missing-dates">
<h2>Finding missing dates<a class="headerlink" href="#finding-missing-dates" title="Permalink to this headline"></a></h2>
<p>Suppose you have some date-stamped data which may (or may not) be missing entries for one or more dates:</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> head -n 10 data/miss-date.csv
</span> date,qoh
2012-03-05,10055
2012-03-06,10486
2012-03-07,10430
2012-03-08,10674
2012-03-09,10880
2012-03-10,10718
2012-03-11,10795
2012-03-12,11043
2012-03-13,11177
</pre></div>
</div>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> wc -l data/miss-date.csv
</span> 1372 data/miss-date.csv
</pre></div>
</div>
<p>Since there are 1372 lines in the data file, some automation is called for. To find the missing dates, you can convert the dates to seconds since the epoch using <code class="docutils literal notranslate"><span class="pre">strptime</span></code>, then compute adjacent differences (the <code class="docutils literal notranslate"><span class="pre">cat</span> <span class="pre">-n</span></code> simply inserts record-counters):</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --from data/miss-date.csv --icsv \
</span><span class="hll"> cat -n \
</span><span class="hll"> then put &#39;$datestamp = strptime($date, &quot;%Y-%m-%d&quot;)&#39; \
</span><span class="hll"> then step -a delta -f datestamp \
</span><span class="hll"> | head
</span> n=1,date=2012-03-05,qoh=10055,datestamp=1330905600,datestamp_delta=0
n=2,date=2012-03-06,qoh=10486,datestamp=1330992000,datestamp_delta=86400
n=3,date=2012-03-07,qoh=10430,datestamp=1331078400,datestamp_delta=86400
n=4,date=2012-03-08,qoh=10674,datestamp=1331164800,datestamp_delta=86400
n=5,date=2012-03-09,qoh=10880,datestamp=1331251200,datestamp_delta=86400
n=6,date=2012-03-10,qoh=10718,datestamp=1331337600,datestamp_delta=86400
n=7,date=2012-03-11,qoh=10795,datestamp=1331424000,datestamp_delta=86400
n=8,date=2012-03-12,qoh=11043,datestamp=1331510400,datestamp_delta=86400
n=9,date=2012-03-13,qoh=11177,datestamp=1331596800,datestamp_delta=86400
n=10,date=2012-03-14,qoh=11498,datestamp=1331683200,datestamp_delta=86400
</pre></div>
</div>
<p>Then, filter for adjacent difference not being 86400 (the number of seconds in a day):</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --from data/miss-date.csv --icsv \
</span><span class="hll"> cat -n \
</span><span class="hll"> then put &#39;$datestamp = strptime($date, &quot;%Y-%m-%d&quot;)&#39; \
</span><span class="hll"> then step -a delta -f datestamp \
</span><span class="hll"> then filter &#39;$datestamp_delta != 86400 &amp;&amp; $n != 1&#39;
</span> n=774,date=2014-04-19,qoh=130140,datestamp=1397865600,datestamp_delta=259200
n=1119,date=2015-03-31,qoh=181625,datestamp=1427760000,datestamp_delta=172800
</pre></div>
</div>
<p>Given this, it’s now easy to see where the gaps are:</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr cat -n then filter &#39;$n &gt;= 770 &amp;&amp; $n &lt;= 780&#39; data/miss-date.csv
</span> n=770,1=2014-04-12,2=129435
n=771,1=2014-04-13,2=129868
n=772,1=2014-04-14,2=129797
n=773,1=2014-04-15,2=129919
n=774,1=2014-04-16,2=130181
n=775,1=2014-04-19,2=130140
n=776,1=2014-04-20,2=130271
n=777,1=2014-04-21,2=130368
n=778,1=2014-04-22,2=130368
n=779,1=2014-04-23,2=130849
n=780,1=2014-04-24,2=131026
</pre></div>
</div>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr cat -n then filter &#39;$n &gt;= 1115 &amp;&amp; $n &lt;= 1125&#39; data/miss-date.csv
</span> n=1115,1=2015-03-25,2=181006
n=1116,1=2015-03-26,2=180995
n=1117,1=2015-03-27,2=181043
n=1118,1=2015-03-28,2=181112
n=1119,1=2015-03-29,2=181306
n=1120,1=2015-03-31,2=181625
n=1121,1=2015-04-01,2=181494
n=1122,1=2015-04-02,2=181718
n=1123,1=2015-04-03,2=181835
n=1124,1=2015-04-04,2=182104
n=1125,1=2015-04-05,2=182528
</pre></div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="footer" role="contentinfo">
&#169; Copyright 2021, John Kerl.
Created using <a href="https://www.sphinx-doc.org/">Sphinx</a> 3.2.1.
</div>
</body>
</html>
Reference in a new issue View git blame Copy permalink