Executing a BigQuery using google workflow to get last modified of a table. getting wrong results in workflow but same works fine in BIGQUERY UI

Question

This is in continuation with my other post where I am getting issues with my workflow. After further debugging I realized that the "last_modified_time" in big query workflow is not showing correct results but same work fine when I execute it in big query UI. Please see below details. Google Cloud Workflow error as "Syntax error: Unclosed string literal

Below is my workflow code just to see what is the value for "last_modified_time"

main:
  steps:
    - getupdatedatetime:
        call: googleapis.bigquery.v2.jobs.query
        args:
          projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
          body:
            useLegacySql: false
            query: >
              SELECT 
              TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
              DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
              FROM `project-dataset.__TABLES__` where table_id='table_name'
        result: queryResult
    - documentFound:
        return: ${queryResult}

The output of the above query in workflow is in json format and is as below

{
  "cacheHit": false,
  "jobComplete": true,
  "jobReference": {
    "jobId": "job__EmSzEzXNUAKBTebWTieYIQVNKf7",
    "location": "EU",
    "projectId": "project_id"
  },
  "kind": "bigquery#queryResponse",
  "rows": [
    {
      "f": [
        {
          **"v": "1.625481329263E9"**
        },
        {
          "v": "2021-07-05"
        }
      ]
    }
  ],
  "schema": {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "last_modified_time",
        "type": "TIMESTAMP"
      },
      {
        "mode": "NULLABLE",
        "name": "creation_date",
        "type": "DATE"
      }
    ]
  },
  "totalBytesProcessed": "0",
  "totalRows": "1"
}

The last_modified_time is not correct. creation_date is fine. Because the last_modified_time is not fine here. my other subworkflows in my workflow is not working fine.

When I execute the same query in big query, I get below results

      SELECT 
      TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
      DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
      FROM `project.dataset.__TABLES__` where table_id='table_name'

Can anyone provide some help and guidance for what I am doing wrong.

Answer 1

The result in your workflow is right. A timestamp is an integer number. In BQ UI, the timestamp is converted to the DateTime format. You can convert the last_modified_time timestamp value to the DateTime format as you want.

I use https://www.epochconverter.com/ to convert your timestamp result to the DateTime format and here is the result: GMT: Monday, July 5, 2021 10:35:29.263.