Bigquery: How can I merge 2 timestamp columns into 1 column?

Question

Does anyone know how to create a single timestamp column from two timestamp columns in Google Bigquery?

I have a table with two timestamp columns and I want to bring these two columns into one single column. The table currently looks like:

id  | user_id | created_at_a             | created_at_b
------------------------------------------------------------------
1   | 1       | 2019-01-24 12:20:00 UTC  | 2019-01-25 01:04:00 UTC
2   | 1       | 2019-01-24 12:20:00 UTC  | 2019-01-25 01:03:00 UTC
3   | 1       | 2019-01-24 12:22:00 UTC  | 2019-01-25 01:03:00 UTC
4   | 1       | 2019-01-24 12:22:00 UTC  | 2019-01-25 01:04:00 UTC
5   | 2       | 2019-01-24 20:48:00 UTC  | 2019-01-24 20:49:00 UTC
6   | 2       | 2019-01-24 11:21:00 UTC  | 2019-01-24 20:49:00 UTC

So... I'm trying to merge these two timestamp columns into one column. My expected result is as follows:

id  | user_id | created_at_a            
----------------------------------------
1   | 1       | 2019-01-24 12:20:00 UTC
2   | 1       | 2019-01-25 01:04:00 UTC
4   | 1       | 2019-01-25 01:03:00 UTC
5   | 1       | 2019-01-24 12:22:00 UTC
6   | 2       | 2019-01-24 20:48:00 UTC
7   | 2       | 2019-01-24 20:49:00 UTC
8   | 2       | 2019-01-24 11:21:00 UTC

Could someone pleeaseeee help me.

Many thanks!

Hi @GrahamPolley I have updated my question to include my desired result. I'd simple like only ONE timestamp column, not two timestamp columns. — Livewire

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-01-28T18:16:49

Below is for BigQuery Standard SQL

#standardSQL
SELECT DISTINCT user_id, created_at
FROM (
  SELECT user_id, 
    ARRAY_CONCAT_AGG([created_at_a, created_at_b]) created_at_ab
  FROM `project.dataset.table`
  GROUP BY user_id
), UNNEST(created_at_ab) created_at

You can test, play with this using sample data from your question as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 user_id, TIMESTAMP '2019-01-24 12:20:00 UTC' created_at_a, TIMESTAMP '2019-01-25 01:04:00 UTC' created_at_b UNION ALL
  SELECT 2, 1, '2019-01-24 12:20:00 UTC', '2019-01-25 01:03:00 UTC' UNION ALL
  SELECT 3, 1, '2019-01-24 12:22:00 UTC', '2019-01-25 01:03:00 UTC' UNION ALL
  SELECT 4, 1, '2019-01-24 12:22:00 UTC', '2019-01-25 01:04:00 UTC' UNION ALL
  SELECT 5, 2, '2019-01-24 20:48:00 UTC', '2019-01-24 20:49:00 UTC' UNION ALL
  SELECT 6, 2, '2019-01-24 11:21:00 UTC', '2019-01-24 20:49:00 UTC' 
)
SELECT DISTINCT user_id, created_at
FROM (
  SELECT user_id, 
    ARRAY_CONCAT_AGG([created_at_a, created_at_b]) created_at_ab
  FROM `project.dataset.table`
  GROUP BY user_id
), UNNEST(created_at_ab) created_at
-- ORDER BY user_id, created_at

with result

Row user_id created_at   
1   1   2019-01-24 12:20:00 UTC  
2   1   2019-01-24 12:22:00 UTC  
3   1   2019-01-25 01:03:00 UTC  
4   1   2019-01-25 01:04:00 UTC  
5   2   2019-01-24 11:21:00 UTC  
6   2   2019-01-24 20:48:00 UTC  
7   2   2019-01-24 20:49:00 UTC

Bigquery: How can I merge 2 timestamp columns into 1 column?

1 Answers