Format event_date and event_timestamp Big query while querying data in custom query

Question

Hi following is my query to fetch some data in big query want to know how can we format date and time stamp

SELECT
  *
FROM (
  SELECT
    (
    SELECT
      x.value
    FROM
      UNNEST(user_properties) x
    WHERE
      x.key='restaurantName'
      AND x.value IS NOT NULL).string_value AS restaurantName,
    event_date AS date,
    event_timestamp AS time,
    event_name AS Event,
    (
    SELECT
      x.value
    FROM
      UNNEST(user_properties) x
    WHERE
      x.key='restaurantId'
      AND x.value IS NOT NULL).string_value AS restaurantID,
    (
    SELECT
      x.value
    FROM
      UNNEST(user_properties) x
    WHERE
      x.key='user_id'
      AND x.value IS NOT NULL).string_value AS user
  FROM
    `analytics.events_*`
  WHERE
    event_name = "OrderSummary"
    AND app_info.id = "app_Id"
  ORDER BY
    event_timestamp ASC)
WHERE
  NOT(restaurantName IS NULL
    OR restaurantID="someName")

i am filtering data right but unable to format

Answer 1

format you expect to have in output? date = 28-11-2019 and time = 04:22:13

Below is for BigQuery Standard SQL

Change/fix is just in below two lines

event_date AS date,
event_timestamp AS time,  

So, instead of above - use below

FORMAT_DATE('%d-%m-%Y', PARSE_DATE('%Y%m%d', event_date)) AS date, 
FORMAT_TIME('%T', TIME(TIMESTAMP_MICROS(event_timestamp))) time,   

and respective output columns will be like

Row date        time     
1   28-11-2019  04:22:13     

Note: I assume that event_date field is of STRING data type. If it actually an INT64 - you just need to ue CAST(event_date AS STRING) instead of event_date

Answer 2

Transform to TIMESTAMP, then FORMAT_TIMESTAMP():

WITH data AS (SELECT 1574914933030017 ms)


SELECT TIMESTAMP_MICROS(ms)
  , FORMAT_TIMESTAMP('%d/%m/%Y %H:%M', TIMESTAMP_MICROS(ms))
FROM data


2019-11-28 04:22:13.030017 UTC
28/11/2019 04:22