'How to add a dummy column with different values to the result of a select query?

I have a table containing a column Bucket, I want to add one more column with some random time stamp values for each Bucket in the select query so as to get a table like below one. How can I achieve this ?

Bucket created_on
bucket-1 2000-06-02 00:37:12
bucket-2 2005-06-02 23:50:19
bucket-5 2020-06-02 12:21:12
bucket-3 2019-06-02 20:28:19
sqlselectgoogle-bigquery


Solution 1:[1]

You can calculate a unixtime between two dates and transform it into a date, adding a randon number makes it random

#standardSQL
WITH parameters AS (
  SELECT  DATE '2010-01-01' start_date, DATE '2022-04-08' finish_date
)
SELECT t1.Bucket, DATE_FROM_UNIX_DATE(CAST(start_date + (finish_date - start_date) * RAND() AS INT64)) random_date
FROM table1 t1  CROSS JOIN parameters p

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nbk

Related Questions