SQL convert yyyymmdd to timestamp in BigQuery?

Question

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;

The MySQL documentation has a page listing all the functions related to dates and times. Surely you could have found the answer there. dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html — Barmar
@Barmar Surely I wouldn't be asking if I hadn't googled this already and found no results. — Joe Scotto
Which database are you actually using, MySQL or BigQuery? Did you add the MySQL tag to get more views? — Tim Biegeleisen

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-01-18T16:47:44

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

SQL convert yyyymmdd to timestamp in BigQuery?

2 Answers