Search code examples
jsondatejqstrptimestrftime

jq date manipulation assistance

I have a mysql date field, called "dateFound". 

dateFound       | date         | YES  |     | NULL              |                             |

I've created a very basic python API that pulls all the data out of MySQL and serves it up via json. In the process, it seems to be converting YYYY-MM-DD into YYYY-MM-DD 00:00:00

mysql> select dateFound from db where item = 111222;
+------------+
| dateFound  |
+------------+
| 2017-11-12 |
+------------+

But in the json output it is:

dateFound   "Sun, 12 Nov 2017 00:00:00 GMT"

I'm trying to isolate how, via jq, I can convert that back YYYY-MM-DD but am really not finding it.

jq -r '["Date Found"], ["-------------"], (.Data[] | [.dateFound]) |  @tsv ' <<<$Report

is a basic approximation of the conversion I'm doing (minus all the other fields), which does give me all the data I need. It's just that date format that I can't get back into YYYY-MM-DD.

I tried inserting a pipe into strptime with the format defined, but that clearly wasn't the right syntax. Looking for a pointer to what I'm missing. I would have thought date manipulation on the data pulled would be easier than this or I am missing something really obvious.

Any assistance appreciated.

Solution
    1. You could use the built-in filter strptime/1 after truncating the string appropriately - see any documentation on the standard C library function, e.g. http://pubs.opengroup.org/onlinepubs/009695399/functions/strptime.html

    For example:

     sub(" [A-Z]+$";"")
 | strptime("%a, %d %b %Y %T")
 | mktime
 | strftime("%Y-%m-%d")
    1. It would probably be simpler and better to fix the problem at the source, i.e. in your Python program.