Wednesday, July 12, 2023

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
create or replace table zzz_timestamp0 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(0) as time1

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.

    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 */

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

Saturday, May 20, 2023

openwrt ssh connection refused


Normal connection attempt
$ ssh root@
Connection to 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 ([]: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 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 dropbear[7691]: Exit (root) from <192.168.1.X:X>: Child failed
Sat May 20 23:02:33 2023 dropbear[7690]: Exit (root) from <192.168.1.X:X>: Disconnect received


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.


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.

I bought a dashcam from Temu for $6.31 January 2024, and here is sample footage that includes three scenes: daytime, dusk, and daytime. ...