DuckDB - Conversion Error: Could not convert Timestamp(MS) to Timestamp(US)

Question

I am trying to convert a script from SQLite to DuckDB, but cannot seem to go around these timestamps and datetime formats...

For example, in SQLite, this code works:

datetime((lastLogon / 10000000) - 11644473600, 'unixepoch') AS lastLogon

in DuckDB, I cannot get myself to find the proper functions...

SELECT epoch_ms(133782998237203223);

results in error

Error: Conversion Error: Could not convert Timestamp(MS) to Timestamp(US)

SQLState: null

ErrorCode: 0

I tried different functions, like

SELECT to_timestamp(133782998237203223)::TIMESTAMPTZ AT TIME ZONE 'UTC'

but still errors

Error: Conversion Error: Could not convert epoch seconds to TIMESTAMP WITH TIME ZONE

Answer 1

The epoch_ms function takes an integral number of milliseconds and returns a TIMESTAMP, but I think your data has 100ns accuracy?

select epoch_ms(133782998237203223 // 100_000);
-- 2012-05-24 03:26:22.372

The to_timestamp function takes a DOUBLE in seconds and returns a TIMESTAMP WITH TIME ZONE:

select to_timestamp(133782998237203223 / 10_000_000);
-- 2012-05-23 20:26:22.372032-07

(displayed in America/Los_Angeles)

Both values will be instants and not naïve (local) timestamps.