How to insert datetime presented as Unix Timestamp

[let4be]

I know that clickhouse stores datetime internally as Unix Timestamp and uses timezone when converting back to text https://clickhouse.tech/docs/en/sql-reference/data-types/datetime/

Is it possible to insert datetime in unix timestamp format(and simply use server's timezone settings)?

I'm also having trouble interacting with library API related to datetime, for example it's not possible to simply insert chrono::Local::now() value without schenanigans, and all my attempts at schenanigans lead to incorrect time zones.

I also see that chrono and chrono_tz aren't re-exported, meaning we have to double track the dependency in our own code(version included)... Can we re-export those 2?

[let4be]

such stuff as ch::types::Value::ChronoDateTime(chrono::Local::now()) fails with

error[E0308]: mismatched types
   --> src/main.rs:182:62
    |
182 |                 created_at: ch::types::Value::ChronoDateTime(Local::now()),
    |                                                              ^^^^^^^^^^^^ expected enum `Tz`, found struct `Local`
    |
    = note: expected struct `chrono::DateTime<Tz>`
               found struct `chrono::DateTime<Local>`

[let4be]

Found this ugly hack that inserts correct date/time in clickhouse

fn now () -> DateTime<Tz> {
    let now = chrono::Local::now();
    chrono_tz::UTC.ymd(now.year(), now.month(), now.day()).and_hms_nano(now.hour(), now.minute(), now.second(), now.nanosecond())
}

everything else either fails to compile or inserts date/time with incorrect timezone

(you then can use now() as an argument to the row! macro for example)

[prk3]

Here is example schema with date time columns:

CREATE TABLE IF NOT EXISTS test_z (
    `time_column_utc` DateTime('UTC'),
    `time_column_warsaw` DateTime('Europe/Warsaw')
)
ENGINE = MergeTree
PARTITION BY time_column_utc
ORDER BY time_column_utc;

First, inserting UNIX timestamp (seconds since 1970-01-01 00:00:00 UTC):

use clickhouse_rs::types::Value;
use chrono_tz::Tz::UTC;

let mut block = Block::new();

block.push(row!{
    time_column_utc: Value::ChronoDateTime(UTC.timestamp_opt(1642180511, 0).unwrap()),
    time_column_warsaw: Value::ChronoDateTime(UTC.timestamp_opt(1642180511, 0).unwrap()),
}).unwrap();

c.insert("test_z", block).await?;

Data in clickhouse:

┌─────time_column_utc─┬──time_column_warsaw─┐
│ 2022-01-14 17:15:11 │ 2022-01-14 18:15:11 │
└─────────────────────┴─────────────────────┘

Second, inserting local time:

use clickhouse_rs::types::Value;
use chrono_tz::Tz::UTC;
use chrono::Local;

let now = Local::now();
println!("Local (Warsaw) time is: {now}"); // Local (Warsaw) time is: 2023-01-14 18:17:09.958814577 +01:00

let mut block = Block::new();

block.push(row!{
    time_column_utc: Value::ChronoDateTime(now.with_timezone(&UTC)),
    time_column_warsaw: Value::ChronoDateTime(now.with_timezone(&UTC)),
}).unwrap();

c.insert("test_z", block).await?;

Data in clickhouse:

┌─────time_column_utc─┬──time_column_warsaw─┐
│ 2023-01-14 17:17:09 │ 2023-01-14 18:17:09 │
└─────────────────────┴─────────────────────┘