CODEWITHSUNDEEP
jsonbashdatetimetimestampformat
AndreasM
AndreasM

Reputation: 11

Convert timestamps in JSON string with Bash commands

I have the following string as below:

{
  "name": {
    "value": "Demo"
  },
  "activity": {
    "value": "CLOSED",
    "timestamp": "2020-11-19T10:58:17.534+0000"
  },
  "state": {
    "value": "OK",
    "timestamp": "2020-11-19T10:58:17.570+0000"
  },
  "lastErrorCode": {
    "value": "NO_MESSAGE",
    "timestamp": "2020-11-19T10:58:17.570+0000"
  }
}

How can I convert all timestamps in that string to another format (Timezone)? Example like (on cmd line):

echo '2020-11-19T10:58:17.534+0000' | xargs date +'%Y-%m-%d %H:%M:%S' -d

results in:

2020-11-19 11:58:17

Upvotes: 1

Views: 465

Answers (2)

Reino
Reino

Reputation: 3443

That string of yours is actually a JSON(-string). Please use a tool that supports JSON and can do dateTime-conversions, like .

First of all, the value of the timestamp-keys is not a valid dateTime. You'd have to change the timezone property 0000 to 00:00 to make it a valid one:

$ xidel -s input.json -e '
  for $x in $json//timestamp return
  dateTime(replace($x,"0000","00:00"))
'
2020-11-19T10:58:17.534Z
2020-11-19T10:58:17.57Z
2020-11-19T10:58:17.57Z

Then to change the timezone to +01:00 use adjust-dateTime-to-timezone():

$ xidel -s input.json -e '
  for $x in $json//timestamp return
  adjust-dateTime-to-timezone(
    dateTime(replace($x,"0000","00:00")),
    duration("PT1H")
  )
'
2020-11-19T11:58:17.534+01:00
2020-11-19T11:58:17.57+01:00
2020-11-19T11:58:17.57+01:00

(You can remove duration("PT1H") if your timezone already is +01:00)

Finally to customize your output use format-dateTime():

$ xidel -s input.json -e '
  for $x in $json//timestamp return
  format-dateTime(
    adjust-dateTime-to-timezone(
      dateTime(replace($x,"0000","00:00")),
      duration("PT1H")
    ),
    "[Y]-[M01]-[D01] [H01]:[m01]:[s01]"
  )
'
2020-11-19 11:58:17
2020-11-19 11:58:17
2020-11-19 11:58:17

If instead you want to update the JSON with these customized dateTimes... that can be done, but requires a more advanced recursive function:

$ xidel -s input.json --xquery '
  declare function local:change-timestamp($a){
    if (exists($a)) then
      if ($a instance of map(*)) then
        map:merge(
          map:keys($a) ! map{
            .:if (.="timestamp") then
              format-dateTime(
                adjust-dateTime-to-timezone(
                  dateTime(replace($a(.),"0000","00:00")),
                  duration("PT1H")
                ),
                "[Y]-[M01]-[D01] [H01]:[m01]:[s01]"
              )
            else
              local:change-timestamp($a(.))
          }
        )
      else
        $a
    else
      ()
  };
  local:change-timestamp($json)
'
{
  "name": {
    "value": "Demo"
  },
  "activity": {
    "value": "CLOSED",
    "timestamp": "2020-11-19 11:58:17"
  },
  "state": {
    "value": "OK",
    "timestamp": "2020-11-19 11:58:17"
  },
  "lastErrorCode": {
    "value": "NO_MESSAGE",
    "timestamp": "2020-11-19 11:58:17"
  }
}

Also check the xidel playground.

Upvotes: 1

ZygD
ZygD

Reputation: 24478

Should be possible using regex. Something like this:

str='{
  "name": {
    "value": "Demo"
  },
  "activity": {
    "value": "CLOSED",
    "timestamp": "2020-11-19T10:58:17.534+0000"
  },
  "state": {
    "value": "OK",
    "timestamp": "2020-11-19T10:58:17.570+0000"
  },
  "lastErrorCode": {
    "value": "NO_MESSAGE",
    "timestamp": "2020-11-19T10:58:17.570+0000"
  }
}'

re='(.*?-[0-9]+-[0-9]+)T([0-9:]+)(\.[0-9+]+)(.*)'
while [[ $str =~ $re ]]; do
  str="${BASH_REMATCH[1]} ${BASH_REMATCH[2]}${BASH_REMATCH[4]}"
done
echo "$str"

Returns:

{
  "name": {
    "value": "Demo"
  },
  "activity": {
    "value": "CLOSED",
    "timestamp": "2020-11-19 10:58:17"
  },
  "state": {
    "value": "OK",
    "timestamp": "2020-11-19 10:58:17"
  },
  "lastErrorCode": {
    "value": "NO_MESSAGE",
    "timestamp": "2020-11-19 10:58:17"
  }
}

Upvotes: 0

Related Questions