CODEWITHSUNDEEP
datetimeawksedjq
Argenis Ramirez
Argenis Ramirez

Reputation: 55

jq parsing date to timestamp

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?

Upvotes: 4

Views: 11468

Answers (3)

Ali ISSA
Ali ISSA

Reputation: 408

Solution with sed :

 sed -e 's/(\([^,]\+\)\(,.*\)/echo "(\$(date -d \1 +%s%3N),\2"/g' | ksh

test :

  <commande_curl> | sed -e 's/(\([^,]\+\)\(,.*\)/echo "(\$(date -d \1 +%s%3N),\2"/g' | ksh

or :

  <commande_curl> > results_curl.txt
  cat results_curl.txt | sed -e 's/(\([^,]\+\)\(,.*\)/echo "(\$(date -d \1 +%s%3N),\2"/g' | ksh

Upvotes: 0

peak
peak

Reputation: 116770

Here is an unportable awk solution. It is not portable because it relies on the system date command; on the system I'm using, the relevant invocation looks like: date -j -f "%Y-%m-%eT%T" STRING "+%s"

awk -F, 'BEGIN{OFS=FS}
  NF==0 { next }
  { sub(/\(/,"",$1);
    cmd="date -j -f \"%Y-%m-%eT%T\" " $1 " +%s";
    cmd | getline $1;
    $1=$1 "000";     # milliseconds
    printf "%s", "(";
    print;
  }' input.txt

Output

(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)

Upvotes: 0

peak
peak

Reputation: 116770

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)

Upvotes: 5

Related Questions