0

What is the best way to convert unixTimestamp to local time in the following scenario?

  • I am using Pub/Sub Subscription to BigQuery Template. Dataflow fetches data in json format from PubSub, does the transformation, inserts into BigQuery
  • Preferably, I want to use UDF for data transformation setup.
  • (For simplicity,) Input data includes only unixTimestamp. Example: {"unixTimestamp": "1612325106000"}
  • Bigquery table has 3 columns:
unix_ts:INTEGER, 
iso_dt:DATETIME, 
local_dt:DATETIME

where unix_ts will keep the input unixTimestamp as it is, iso_dt will keep the UTC datetime, local_dt will keep Europe/Frankfurt datetime. Example: 1612325106000, 2021-02-03T04:05:06, 2021-02-03T05:05:06

I was hoping that the following would work, but did not. unix_ts and iso_dt results as expected but local_dt is the part that leads to a failure. What would be your suggestion? Thanks

function transform(inJson) {
   var input = JSON.parse(inJson);

   var v_date = new Date(parseInt(input.unixTimestamp));

   var v_iso_dt = v_date.toISOString().replace('Z','');
   var v_local_dt = v_date.toLocaleString('en-US', {timeZone: 'Europe/Frankfurt'});

   var output = {
       "unix_ts": input.unixTimestamp || null,
       "iso_dt": v_iso_dt  || null,
       "local_dt": v_local_dt  || null
   };
}

Edit: (Changed) 1612325106 to 1612325106000 in the example.

Drawing Expedition
  • 9
  • 2
  • What error or incorrect behavior do you get when you run that pipeline? – danielm Feb 03 '21 at 22:31
  • Invalid datetime string \"Wed Feb 03 2021 04:05:06 GMT+0000 (UTC)\" This is for local_dt field. I understand that the format does not fit with the datetime type. It should be something like 2021-02-03T04:05:06 but I dont get why it show UTC value. I need to come to a conclusion like 2021-02-03T05:05:06 – Drawing Expedition Feb 04 '21 at 00:52
  • Please, see [this](https://stackoverflow.com/questions/49330139/date-toisostring-but-local-time-instead-of-utc) SO question, I think it can be helpful. – jccampanero Feb 08 '21 at 19:04

0 Answers0