JQ: Transform UNIX Timestamp to Datetime

Question

I use actually JQ1.5 under a Windows 10 environment to transform several json files for an import to a MS SQL database. Part of the data are formatted in UNIX timestamp and I need to transform those data to ISO 8601 Format.

Following command i use actually for the transformation of the data:

jq '
[
  { nid, title, nights, zone: .zones[0].title} + 
  (.sails[] | { sails_nid: .nid, arrival, departure } ) + 
  (.sails[].cabins[] | 
    { cabintype: .cabinType.kindName, 
      cabinid:   .cabinType.nid,  
      catalogPrice, 
      discountPrice, 
      discountPercentage, 
      currency 
    }
  )
]
' C:\Import\dreamlines_details.json > C:\Import\import_sails.json

Arrival and departure are the data that are in Unix time formated.

Data:

[
  {
    "nid": 434508,
    "title": "Die schönsten Orte unserer Welt",
    "nights": 121,
    "zone": "Weltreise",
    "sails_nid": 434516,
    "arrival": 1525644000,
    "departure": 1515193200,
    "cabintype": "Innenkabine",
    "cabinid": 379723,
    "catalogPrice": 17879,
    "discountPrice": 9519,
    "discountPercentage": 0.4675876726886291,
    "currency": "EUR"
  },
  {
    "nid": 434508,
    "title": "Die schönsten Orte unserer Welt",
    "nights": 121,
    "zone": "Weltreise",
    "sails_nid": 434516,
    "arrival": 1525644000,
    "departure": 1515193200,
    "cabintype": "Innenkabine",
    "cabinid": 379730,
    "catalogPrice": 18599,
    "discountPrice": 10239,
    "discountPercentage": 0.44948653153395346,
    "currency": "EUR"
  }
]

I experimented with built in operator "todate" and "strftime". But get only parsing Errors.

Answer 1

Use todateiso8601 function:

jq '.[].arrival |= todateiso8601 | .[].departure |= todateiso8601' C:\Import\import_sails.json

The output (for your input fragment):

[
  {
    "nid": 434508,
    "title": "Die schönsten Orte unserer Welt",
    "nights": 121,
    "zone": "Weltreise",
    "sails_nid": 434516,
    "arrival": "2018-05-06T22:00:00Z",
    "departure": "2018-01-05T23:00:00Z",
    "cabintype": "Innenkabine",
    "cabinid": 379723,
    "catalogPrice": 17879,
    "discountPrice": 9519,
    "discountPercentage": 0.4675876726886291,
    "currency": "EUR"
  },
  {
    "nid": 434508,
    "title": "Die schönsten Orte unserer Welt",
    "nights": 121,
    "zone": "Weltreise",
    "sails_nid": 434516,
    "arrival": "2018-05-06T22:00:00Z",
    "departure": "2018-01-05T23:00:00Z",
    "cabintype": "Innenkabine",
    "cabinid": 379730,
    "catalogPrice": 18599,
    "discountPrice": 10239,
    "discountPercentage": 0.44948653153395346,
    "currency": "EUR"
  }
]