how to insert random timestamp in snowflake timestamp field

user_420558 0
Viewed 71

I am trying the following SQL but I get an error:

CREATE TABLE slipstream( visit_timestamp time);
INSERT INTO slipstream values(dateadd(second, uniform(1, 10, random()), current_time()));

Error:

Invalid expression [DATE_ADDSECONDSTOTIME(CAST(UNIFORM(1, 10, RANDOM()) AS NUMBER(2,0)), '12:52:29.050000000')] in VALUES clause

Please advise

snowflake-cloud-data-platform
2 Answers

Using INSERT - SELECT pattern:

INSERT INTO slipstream (visit_timestamp)
SELECT dateadd(second, uniform(1, 10, random()), current_time());

Output:

enter image description here

user_50708790

The VALUES requires constants and allows for simple casting that the SQL parser can do, thus in the values line you can have '2022-06-21'::date and that will correctly cast to DATE.

SELECT column1, system$typeof(column1) FROM VALUES 
    ('2022-06-21'::date),
    ('2022-05-21'::date);

gives:

COLUMN1 SYSTEM$TYPEOF(COLUMN1)
2022-06-21 DATE[SB4]
2022-05-21 DATE[SB4]

And thus for this workflow it would have been valid to use:

INSERT INTO slipstream VALUES 
    ('2022-06-21'::timestamp),
    ('2022-05-21'::timestamp);
number of rows inserted
2

But complex function calls need to be executed SQL, thus as Lukasz mentioned the need for the INSERT/SELECT pattern.

Now you can be clever and put a VALUES on the SELECT to data drive the parameters to those functions (I was hoping to be clever as using the values to uniform, but those need to be constants, so settled for an offset)

CREATE TABLE slipstream( visit_timestamp time);

INSERT INTO slipstream 
SELECT dateadd(second, uniform(1, 10, random() ) + column1, current_time() )
    FROM VALUES
        (0),
        (-10), 
        (-20);
number of rows inserted
3 
SELECT * FROM slipstream;
VISIT_TIMESTAMP
16:18:04
16:17:55
16:17:42
user_439920
Related