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

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

CodePudding user response：

Use: How do I generate a random number for each row in a T-SQL select?

SELECT
   Bucket,
   DATEADD(DAY,-ABS(CHECKSUM(NewId())),GETDATE()) created_on
FROM yourtable

The column created on will get random values between today(GETDATE()), and 10 days befor that day.

CodePudding user response：

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