I am using the fantastic jq to manipulate a REST API's json, into a csv for upsertting into another database system, via its API. Once in the target system, I'm doing some date math, including rounding time stamps to 30 min intervals to allow me to do group them, for standard deviation calculation. The problem is, there is a lot of data and the database chokes when it has to round every record and then do std dev calculations on each.
- Sample.json shows a small sample of what the input data looks like, but actually it's just stdout from the curl command in the shell script.
- example-initial.sh shows the important bit of the initial shell script, that uses curl to authenticate against the data location's API, and then use jq to add a couple of columns and export to csv. It works like a charm.
- ACME-X1-A.csv is an example of the output CSV file, that's then upsertted into the target db.
Instead of doing compute extensive calculations in the target db every time the user queries the db, thus slowing the UI down, I'd like to pre-calculate some number that will let me group all the data in a 30 min window.
My concept is to do it with MOD, which jq is said to support via % operator.
I want to convert the timestamps to unix time, get the modulus dividing by 1800 (seconds in 30 min), then subtract the modulus from the unix time to get a value that groups the entries in 30 min chunks.
Idea.csv illustrates the concept. I can group on the "Epoch-Modulus", which will be a new field in the db, which will just upload as text, no date-time calculation needed in there.
Shortening the jq code and assuming I'll pipe the json to it:
jq '.' | jq 'map(. + {deviceId:"6e09a5d5-6e1f-7672-4ff0486d588074d8"} + {sensorType:"A"})' | jq -r '["CreatedOn","Value","AlarmId","AlarmName","deviceId","Type"], (.[] | [.CreatedOn, .Value, .AlarmId, .AlarmName, .deviceId, .sensorType]) | @csv' > output.csv
... my question is, how do I manipulate the timestamp I have, with jq, to convert it to unix time, store that as say $myepoch then do $myepoch - ($myepoch % 1800)? I'd like to add those values as a new extra column in the CSV.
I've tried a number of things using fromdate in https://jqplay.org/s/u3_pDNyYfD but, to no avail. I just keep getting errors.
IRC channel #jq user "go|dfish" pointed out that fromdate expects a timezone, so, if we add the timezone to the timestamp and pass that, we can do the calculation:
map(.CreatedOn + "Z" | fromdate - (fromdate % 1800))
Thanks go|dfish!
Cleanup timestamps with milliseconds:
map((.CreatedOn | split(".") | .[0]) + "Z" | fromdate - (fromdate % 1800))
The example-final.sh shell script shows the working solution in situ.