-2

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
xvzf
  • 137
  • 1
  • 10
  • is there any logic for those random timestamps? like within the specific range for example? or how they should be distributed? etc. – Mikhail Berlyant Apr 08 '22 at 18:56
  • They're completely random. – xvzf Apr 11 '22 at 05:04
  • Hello @xvzf. Do the existing answers satisfy your requirement? If they don't, can you please explain why? – Vishal K Apr 12 '22 at 13:50
  • I got some idea after looking the below approaches, though none of them was exact solution to my problem but I was able to get the query working using the below answers as pointers. – xvzf Apr 13 '22 at 08:49

1 Answers1

-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
nbk
  • 45,398
  • 8
  • 30
  • 47