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.

openwrt ssh connection refused

Symptom

Normal connection attempt

$ ssh root@192.168.1.1
Connection to 192.168.1.1 closed.

End of log with verbose ssh

debug1: Authentications that can continue: publickey,password
debug1: Next authentication method: publickey
debug1: Offering public key: /home/z/.ssh/id_ed25519 ED25519 SHA256:XX//XX/XX agent
debug1: Server accepts key: /home/z/.ssh/id_ed25519 ED25519 SHA256:XX//XX/XX agent
Authenticated to 192.168.1.1 ([192.168.1.1]:22) using "publickey".
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: pledge: filesystem
debug1: Sending environment.
debug1: channel 0: setting env LANG = "en_US.UTF-8"
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to 192.168.1.1 closed.
Transferred: sent 2488, received 1012 bytes, in 0.0 seconds
Bytes per second: sent 346691.3, received 141017.5
debug1: Exit status 1

System log snippet in OpenWRT's luci interface

Sat May 20 23:02:33 2023 authpriv.notice dropbear[7690]: Pubkey auth succeeded for 'root' with ssh-ed25519 key SHA256:XX//XX/XX from 192.168.1.X:X
Sat May 20 23:02:33 2023 authpriv.info dropbear[7691]: Exit (root) from <192.168.1.X:X>: Child failed
Sat May 20 23:02:33 2023 authpriv.info dropbear[7690]: Exit (root) from <192.168.1.X:X>: Disconnect received

Background

A few weeks ago, I installed OpenWRT 22.03.3 on my Belkin RT3200. SSH and everything else was working fine until I updated it to OpenWRT 22.03.5, and then immedietly SSH refused to connect.

Solution

I read a GitHub conversation about a similar problem. In their case, zsh was missing, and I remembered that earlier I installed Bash. I reinstalled it via LuCI, and then SSH worked again.