jq parsing date to timestamp

Question

I have the following script:

curl -s -S 'https://bittrex.com/Api/v2.0/pub/market/GetTicks?marketName=BTC-NBT&tickInterval=thirtyMin&_=1521347400000' | jq -r '.result|.[] |[.T,.O,.H,.L,.C,.V,.BV] | @tsv | tostring | gsub("\t";",") | "(\(.))"'

This is the output:

(2018-03-17T18:30:00,0.00012575,0.00012643,0.00012563,0.00012643,383839.45768188,48.465051)
(2018-03-17T19:00:00,0.00012643,0.00012726,0.00012642,0.00012722,207757.18765437,26.30099514)
(2018-03-17T19:30:00,0.00012726,0.00012779,0.00012698,0.00012779,97387.01596624,12.4229077)
(2018-03-17T20:00:00,0.0001276,0.0001278,0.00012705,0.0001275,96850.15260027,12.33316229)

I want to replace the date with timestamp.

I can make this conversion with date in the shell

date -d '2018-03-17T18:30:00' +%s%3N
1521325800000

I want this result:

(1521325800000,0.00012575,0.00012643,0.00012563,0.00012643,383839.45768188,48.465051)
(1521327600000,0.00012643,0.00012726,0.00012642,0.00012722,207757.18765437,26.30099514)
(1521329400000,0.00012726,0.00012779,0.00012698,0.00012779,97387.01596624,12.4229077)
(1521331200000,0.0001276,0.0001278,0.00012705,0.0001275,96850.15260027,12.33316229)

This data is stored in MySQL.

Is it possible to execute the date conversion with jq or another command like awk, sed, perl in a single command line?

Answer 1

Here is an all-jq solution that assumes the "Z" (UTC+0) timezone.

In brief, simply replace .T by:

((.T + "Z") | fromdate | tostring + "000")

To verify this, consider:

timestamp.jq

[splits("[(),]")]
| .[1] |= ((. + "Z")|fromdate|tostring + "000")  # milliseconds
| .[1:length-1]
| "(" + join(",") + ")"

Invocation

jq -rR -f timestamp.jq  input.txt

Output

(1521311400000,0.00012575,0.00012643,0.00012563,0.00012643,383839.45768188,48.465051)
(1521313200000,0.00012643,0.00012726,0.00012642,0.00012722,207757.18765437,26.30099514)
(1521315000000,0.00012726,0.00012779,0.00012698,0.00012779,97387.01596624,12.4229077)
(1521316800000,0.0001276,0.0001278,0.00012705,0.0001275,96850.15260027,12.33316229)