Convert timestamp from API into date and add 3 hours-CodePudding

I have the following timestamps from an API call:

2022-05-22T13:53:42.010238Z
2022-05-22T11:53:30.910039Z
2022-05-22T11:46:43.904101Z
2022-05-22T11:46:27.815341Z
2022-05-22T11:46:12.581602Z
2022-05-22T11:41:56.246782Z
2022-05-22T11:41:39.443423Z

I would like to convert them into readable dates example: 22/05/2022-13:53:42 and also add 3 hours to them.

CodePudding user response：

You can try below formula-

=SUM(SPLIT(SUBSTITUTE(A1,"Z",""),"T")) TIME(3,0,0)

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const data = [
    "2022-05-22T13:53:42.010238Z",
    "2022-05-22T11:53:30.910039Z",
    "2022-05-22T11:46:43.904101Z",
    "2022-05-22T11:46:27.815341Z",
    "2022-05-22T11:46:12.581602Z",
    "2022-05-22T11:41:56.246782Z",
    "2022-05-22T11:41:39.443423Z",
  ];
  const add = 3 * 60 * 60 * 1000;
  const res = data.map(e => Utilities.formatDate(new Date(new Date(e).getTime()   add), "GMT", "dd/MM/yyyy-HH:mm:ss"));
  console.log(res)
}

When this script is run, the following result is showin in the log.

  [
    '22/05/2022-16:53:42',
    '22/05/2022-14:53:30',
    '22/05/2022-14:46:43',
    '22/05/2022-14:46:27',
    '22/05/2022-14:46:12',
    '22/05/2022-14:41:56',
    '22/05/2022-14:41:39'
  ]

References:

CodePudding user response：

Convert Timestamp and add 3 hours

function lfunko() {
  const dA = [
    "2022-05-22T13:53:42.010238Z", "2022-05-22T11:53:30.910039Z", "2022-05-22T11:46:43.904101Z", "2022-05-22T11:46:27.815341Z", "2022-05-22T11:46:12.581602Z", "2022-05-22T11:41:56.246782Z", "2022-05-22T11:41:39.443423Z"];
  dtA = dA.reduce((a, c, i) => {
    let dt = new Date(c);
    dt.setHours(new Date(c).getHours()   3);
    a.dts.push(dt);
    return a;
  }, { dts: [] }).dts;
  Logger.log(JSON.stringify(dtA))
}

Execution log
10:06:46 PM Notice  Execution started
10:06:45 PM Info    ["2022-05-22T16:53:42.010Z","2022-05-22T14:53:30.910Z","2022-05-22T14:46:43.904Z","2022-05-22T14:46:27.815Z","2022-05-22T14:46:12.581Z","2022-05-22T14:41:56.246Z","2022-05-22T14:41:39.443Z"]
10:06:47 PM Notice  Execution completed