CODEWITHSUNDEEP
sqltimestampgoogle-bigquery
Joe Scotto
Joe Scotto

Reputation: 10887

SQL convert yyyymmdd to timestamp in BigQuery?

I'm attempting to convert a string to a timestamp within SQL. The question is really quite simple, how can I convert this string into a timestamp that starts at midnight on that day?

Within my database I also have a field stored in timestamp_micros either one of these could work and I think converting the micros to a timestamp would be easier than the string.

For example 20170118 => timestamp

Query:

WITH allTables as (
  SELECT
      event.date as date,
      count(*) as totalSessions,
      count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
  FROM `namehiddenonlyhere.*`
  CROSS JOIN
      UNNEST(event_dim) AS event
  WHERE
      event.name = 'session_start'
  AND 
      event.date <= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  AND
      event.date >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
  GROUP BY event.date

  UNION ALL 
  SELECT
      event.date as date,
      count(*) as totalSessions,
      count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
  FROM `namehiddenonlyhere.*`
  CROSS JOIN
      UNNEST(event_dim) AS event
  WHERE
      event.name = 'session_start'
  AND 
      event.date <= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  AND
      event.date >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
  GROUP BY event.date
) SELECT 'all apps' as target, date as datapoint_time, totalSessions datapoint_value FROM allTables;

Upvotes: 3

Views: 11789

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33765

You can use PARSE_DATE or PARSE_TIMESTAMP (the format string will be the same) to get a date or a timestamp. For example:

SELECT
  d,
  PARSE_DATE('%Y%m%d', d) AS date,
  PARSE_TIMESTAMP('%Y%m%d', d) AS timestamp
FROM UNNEST(['20170117', '20161231']) AS d;

You can read more about the format strings in the documentation. There are sections for date and for timestamp, which apply to PARSE_DATE and PARSE_TIMESTAMP respectively.

Edit: from your updated question, you could change your query to something like this:

WITH allTables as (
  SELECT
      event.date as date,
      count(*) as totalSessions,
      count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
  FROM `namehiddenonlyhere.*`
  CROSS JOIN
      UNNEST(event_dim) AS event
  WHERE
      event.name = 'session_start'
  AND 
      event.date <= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  AND
      event.date >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
  GROUP BY event.date

  UNION ALL 
  SELECT
      event.date as date,
      count(*) as totalSessions,
      count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
  FROM `namehiddenonlyhere.*`
  CROSS JOIN
      UNNEST(event_dim) AS event
  WHERE
      event.name = 'session_start'
  AND 
      event.date <= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  AND
      event.date >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
  GROUP BY event.date
) SELECT 'all apps' as target, PARSE_DATE('%Y%m%d', date) as datapoint_time, totalSessions datapoint_value FROM allTables;

The only modification I made was to change:

date AS datapoint_time

to:

PARSE_DATE('%Y%m%d', date) as datapoint_time

Upvotes: 9

remmendo
remmendo

Reputation: 19

Did you try to use this expression?

SELECT CONVERT(DATETIME,'20170118')

Upvotes: -2

Related Questions