jq dates and unix timestamps

Question

So I have a data with bunch of unix timestamp values (in milliseconds). Something like this:

{
    "id": "f6922fd5-4f97-4113-820e-b45eba0ae236",
    "published_at": 1461624333859,
    "tracking_id": "a85d5ed5-5efa-461b-aae0-beb2098c0ff7",
}, {
    "id": "835d412f-5162-440c-937b-7276f22c4eb9",
    "published_at": 1461625249934,
    "tracking_id": "86472ba2-ce5f-400f-b42a-5a0ac155c42c",
}, {
    "id": "bc2efcac-67a0-4855-856a-f31ce5e4618e",
    "published_at": 1461625253393,
    "tracking_id": "c005398f-07f8-4a37-b96d-9ab019d586c2",
}

And very often we need to search for rows within a certain date. Is it possible to query with jq, providing human readable dates e.g. 2016-04-25. Also I wonder if the other way around possible, to make jq show published_at values in human readable form?

For example this works:

$ echo 1461624333 | jq 'todate'   
"2016-04-25T22:45:33Z"

although it has to be in seconds, not milliseconds

Answer 1

Sure! Your provided input is not valid JSON, but I'm going to assume the trailing commas on those objects are removed and the objects are wrapped in an array, which would be the root object of the JSON document.

First, we can transform the millisecond-precision UNIX dates into second-precision, which is what jq's date functions expect, and then convert that to the human-readable dates you expect:

.[].published_at |= (. / 1000 | strftime("%Y-%m-%d"))

Then, we select only those elements whose dates match:

map(select(.published_at == $date))

Lastly, we put it all together, taking the $date variable from the command-line:

jq --arg date "2016-04-25" '.[].published_at |= (. / 1000 | strftime("%Y-%m-%d")) | map(select(.published_at == $date))' stuff.json

Answer 2

jq 1.5 has standard time-and-date functions such as strftime, as documented in the online manual. However support for TZ is extremely limited and/or unreliable, as illustrated here:

$ echo $TZ

$ jq -n '123 | strftime("%B %d %Y %I:%M%p %Z")'
"January 01 1970 12:02AM EST"

TZ='Asia/Kolkata' jq -n '123 | strftime("%B %d %Y %I:%M%p %Z")'
"January 01 1970 12:02AM IST"

strflocaltime

If your jq has strflocaltime:

TZ=Asia/Kolkata jq -n '123|strflocaltime("%Y-%m-%dT%H:%M:%S %Z")'
"1970-01-01T05:32:03 IST"