How to query for rows between two dates ? #856
-
|
Hello What is the optimal way to query data set [1], where column with dates exists, to return rows between two dates? Am trying to get rows between 2 dates. As for poorman solution:
Using hardcoded begin/end epochs (1676072406,1676074620): cat data | \
qsv apply datefmt time_received --formatstr '%s' -c time_received_epoch |\
qsv luau map in_range '(tonumber(time_received_epoch) >= 1676072406 and tonumber(time_received_epoch) <= 1676074620)'or a bit more user-friendly begin=$(echo "2023-02-10 23:40:06.229" |qsv apply -n datefmt 1 --formatstr '%s')
end=$(echo "2023-02-11 00:17" |qsv apply -n datefmt 1 --formatstr '%s')
cat data | \
qsv apply datefmt time_received --formatstr '%s' -c time_received_epoch | \
qsv luau map in_range "(tonumber(time_received_epoch) >= $begin and tonumber(time_received_epoch) <= $end)"Sample result in [2]. From there on can search for Q: 👉 Is there a better way to reach the same ? [1] cat >data<<'EOF'
id,state,time_received
36462310-a899-71ed-0028-8a2540b40000,CLOSED,2023-02-10 23:18:22.444
7368680c-a899-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:20:06.013
867cdd24-a899-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:20:38.025
fa2b3720-a899-71ed-034f-844009280000,CLOSED,2023-02-10 23:23:50.889
1be17924-a89a-71ed-1b58-8a8732150000,CLOSED,2023-02-10 23:24:48.663
25a7821e-a89a-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:25:04.059
38bbaa06-a89a-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:25:36.069
4a070efe-a89a-71ed-1ff6-8a681f960000,CLOSED,2023-02-10 23:26:05.123
ae719350-a89a-71ed-1d81-844009620000,CLOSED,2023-02-10 23:28:53.351
bcad38c0-a89a-71ed-1da4-844008b10000,CLOSED,2023-02-10 23:29:17.286
d880b4dc-a89a-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:30:04.117
e932f9c0-a89a-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:30:32.129
8b599308-a89b-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:35:04.173
9c0b3eb8-a89b-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:35:32.18
3f63385e-a89c-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:40:06.229
50152a7c-a89c-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:40:35.291
61dd4776-a89c-71ed-1ff7-8a681f960000,CLOSED,2023-02-10 23:41:04.107
f10a75fe-a89c-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:45:04.279
02ee9228-a89d-71ed-055a-8a2503eb0000,CLOSED,2023-02-10 23:45:34.295
a3e352b8-a89d-71ed-055b-8a2503eb0000,CLOSED,2023-02-10 23:50:05.403
b494e8c4-a89d-71ed-055b-8a2503eb0000,CLOSED,2023-02-10 23:50:32.341
57ec46a2-a89e-71ed-055b-8a2503eb0000,CLOSED,2023-02-10 23:55:06.383
69cf5936-a89e-71ed-055b-8a2503eb0000,CLOSED,2023-02-10 23:55:36.392
79b5f918-a89e-71ed-1ff8-8a681f960000,CLOSED,2023-02-10 23:56:03.109
0ac46b24-a89f-71ed-055b-8a2503eb0000,CLOSED,2023-02-11 00:00:07.48
1b7727cc-a89f-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:00:34.412
3331eadc-a89f-71ed-0261-8a2501e00000,CLOSED,2023-02-11 00:01:14.277
bc6e0308-a89f-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:05:05.574
ce517c80-a89f-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:05:34.515
6f4828fa-a8a0-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:10:04.566
812b6f3c-a8a0-71ed-055c-8a2503eb0000,ACTIVE,2023-02-11 00:10:34.577
91903e48-a8a0-71ed-1ff9-8a681f960000,CLOSED,2023-02-11 00:11:02.152
ad6d1686-a8a0-71ed-0eb4-8a681f960000,CLOSED,2023-02-11 00:11:49.827
22c78092-a8a1-71ed-1ade-8a8421c90000,CLOSED,2023-02-11 00:15:05.716
2351344a-a8a1-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:15:06.615
39f8ac5a-a8a1-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:15:45.678
5019e99a-a8a1-71ed-16e2-8a25014a0000,ACTIVE,2023-02-11 00:16:21.793
ca5329b0-a8a1-71ed-1558-8a25000c0000,CLOSED,2023-02-11 00:19:46.816
d6295368-a8a1-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:20:06.666
e80c39ce-a8a1-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:20:36.675
8900f1f8-a8a2-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:25:06.714
9c14f802-a8a2-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:25:38.722
a9ffc320-a8a2-71ed-1ff9-8a681f960000,CLOSED,2023-02-11 00:26:02.11
3aa83f9c-a8a3-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:30:04.767
4a28e46c-a8a3-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:30:30.777
93f8aafa-a8a3-71ed-0b25-8a2500290000,ACTIVE,2023-02-11 00:32:36.649
ed805f8c-a8a3-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:35:05.837
fe31fdb8-a8a3-71ed-055c-8a2503eb0000,CLOSED,2023-02-11 00:35:32.826
a189971e-a8a4-71ed-055d-8a2503eb0000,CLOSED,2023-02-11 00:40:06.868
EOF
[2] |
Beta Was this translation helpful? Give feedback.
Answered by
jqnatividad
Mar 14, 2023
Replies: 2 comments 2 replies
-
|
Hi @slonyik , I actually bundled LuaDate so you just need to And perhaps, you can take advantage of the full-pledged data-wrangling capabilities of |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
And to directly answer your question @slonyik , here's the qsv luau filter -x \
'local date = require "date";tr_date = date(time_received);lower_date = date("2023-02-10 11:40:06");higher_date = date("2023-02-11 12:17:00");return tr_date >= lower_date and tr_date <= higher_date' \
data.csvand the result is: |
Beta Was this translation helpful? Give feedback.
2 replies
-
|
And here's a more efficient version @slonyik that requires date and initializes
with the BEGIN {
date = require "date";
lower_date = date("2023-02-10 11:40:06");
higher_date = date("2023-02-11 12:17:00");
}!
-- MAIN script loop
tr_date = date(time_received);
return tr_date >= lower_date and tr_date <= higher_date;
|
Beta Was this translation helpful? Give feedback.
-
|
Wow, thank you Joel. |
Beta Was this translation helpful? Give feedback.
Answer selected by
jqnatividad
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
And to directly answer your question @slonyik , here's the
luaucommand that uses the LuaDate library:qsv luau filter -x \ 'local date = require "date";tr_date = date(time_received);lower_date = date("2023-02-10 11:40:06");higher_date = date("2023-02-11 12:17:00");return tr_date >= lower_date and tr_date <= higher_date' \ data.csvand the result is: