CODEWITHSUNDEEP
linuxbashtimezonejq
R.S
R.S

Reputation: 45

jq convert timezone in bash script

I have a query as below:

dump=$(jq -j --raw-output '.aggregations | ."2" | .buckets[] | "\(.key),",
                                       (."5" | .buckets[] | "\(.key),",
                                       (."3" | .buckets[] | "\(.key),",
                                       (."4" | .buckets[] | "\(.key),",
                                       (."8" | .buckets[] | "\(.key),",
                                       (."6" | .buckets[] | "\(.key),",
                                       (."7" | .buckets[] | "\(.key),",
                                       (."9" | .buckets[] | "\(.key),",
                                       (."10" | .buckets[] | "\(.key),",
                                       (."11" | .buckets[] | "\(.key),",
                                       (."12" | .buckets[] | "\(.key),",
                                       (."13" | .buckets[] | "\(.key),",
                                       (."14" | .buckets[] | "\(.key),",
                                       (."15" | .buckets[] | "\(.key),",
                                       (."1" | ."hits" | .hits[] | ."_source" |
                                           "\(."@timestamp")" | "\n"))))))))))))))' <<< "$cl_report")
echo -e "${dump}" >> report.csv

timestamp is ISO8601 format.

I want to convert the timestamp from UTC to Madrid time.

What I want is something like this:

dump=$(jq -j --raw-output '.aggregations | ."2" | .buckets[] | "\(.key),",
                                       (."5" | .buckets[] | "\(.key),",
                                       (."3" | .buckets[] | "\(.key),",
                                       (."4" | .buckets[] | "\(.key),",
                                       (."8" | .buckets[] | "\(.key),",
                                       (."6" | .buckets[] | "\(.key),",
                                       (."7" | .buckets[] | "\(.key),",
                                       (."9" | .buckets[] | "\(.key),",
                                       (."10" | .buckets[] | "\(.key),",
                                       (."11" | .buckets[] | "\(.key),",
                                       (."12" | .buckets[] | "\(.key),",
                                       (."13" | .buckets[] | "\(.key),",
                                       (."14" | .buckets[] | "\(.key),",
                                       (."15" | .buckets[] | "\(.key),",
                                       (."1" | ."hits" | .hits[] | ."_source" | "\ 
                                       (."@timestamp") as $tstamp | "TZ Europe/Madrid" date -d $tstamp" | \n"))))))))))))))' <<< "$cl_report")

Bash throws error complaining about " and ' etc etc.

What is the correct way of doing this conversion?

Ok. It seems there is a bug with jq 1.5. strftime etc does not read TZ properly in this version.

jq 1.6 can help:

report_dump=$(TZ=Erope/Madrid jq1.6 -j --raw-output '.aggregations | ."2" | .buckets[] | "\(.key),",
                                       (."5" | .buckets[] | "\(.key),",
                                       (."3" | .buckets[] | "\(.key),",
                                       (."4" | .buckets[] | "\(.key),",
                                       (."8" | .buckets[] | "\(.key),",
                                       (."6" | .buckets[] | "\(.key),",
                                       (."7" | .buckets[] | "\(.key),",
                                       (."9" | .buckets[] | "\(.key),",
                                       (."10" | .buckets[] | "\(.key),",
                                       (."11" | .buckets[] | "\(.key),",
                                       (."12" | .buckets[] | "\(.key),",
                                       (."13" | .buckets[] | "\(.key),",
                                       (."14" | .buckets[] | "\(.key),",
                                       (."15" | .buckets[] | "\(.key),",
                                       (."1" | ."hits" | .hits[] | ."_source" | .["@timestamp"] | sub(".[0-9]+Z$"; "Z") | fromdateiso8601 | strflocaltime("%Y-%m-%dT%H:%M:%S %Z"), "\n"))))))))))))))' <<< "$cl_report")

As shown above, I piped the stripped timestamp to fromdateiso8601 to get seconds and then piped into strflocaltime that utilizes TZ to get the local time.

Upvotes: 0

Views: 1703

Answers (1)

peak
peak

Reputation: 116880

Unfortunately, jq's handling of local time zones has been problematic and you may need to adapt the following according to the specifics of your version of jq (i.e. version number and platform).

Here's an illustration using the TZ environment variable, e.g.

TZ=Europe/Madrid jq -n -f program.jq

where program.jq contains:

{"@timestamp": "2020-05-04T18:02:13Z"}
| .["@timestamp"]
| fromdateiso8601   # converts to seconds
| gmtime
| strftime("%Y-%m-%dT%H:%M:%S%Z")

Output: "2020-05-04T19:02:13CET"

Note: %Z becomes CET.

Upvotes: 1

Related Questions