最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin0浏览0评论

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

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

Share Improve this question edited Jan 24 at 16:25 CommunityBot 11 silver badge asked Jan 18 at 13:17 user29247426user29247426 331 silver badge4 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 4

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.

发布评论

评论列表(0)

  1. 暂无评论