Timestamp precision in Snowflake

Timestamps in Snowflake have precisions 0 to 9 with a default of 9, which is a nanosecond, but the Snowflake documentation is not clear on precisions 0 to 8.

Storage difference

I did an empircal test by creating tables. Each table had one million rows and one column with random timestamps. The values have an original precison of one nanosecond, and I used random values because otherwise Snowflake would compress down any number of rows with the same values to a few KB.

create or replace table zzz_timestamp9 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;
create or replace table zzz_timestamp0 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(0) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;

The storage difference for 1 million rows was 3.5MB vs 7.0MB.

Precision difference

Again, I generated random rows and then copied the value into columns with varied precisions.

select
    dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as "precision 9",
    "precision 9"::timestamp(3) as "precision 3",
    "precision 9"::timestamp(2) as "precision 2",
    "precision 9"::timestamp(1) as "precision 1",
    "precision 9"::timestamp(0) as "precision 0",
    datediff(ms, "precision 3", "precision 9") as "Precisions 9 vs 3 in milliseconds", /* alwayz zero */
    datediff(second, "precision 0", "precision 9") as "Precisions 9 vs 0 in seconds" /* alwayz zero */
from TABLE(GENERATOR(ROWCOUNT => 10))

Precision 0 is 1 second, precision 1 is 100 ms, precision 2 is 10 ms, precision 3 is 1 ms, etc.