最小情况
如果min(ticktime)::TIMESTAMP2014-01-02 01:14:45.5,我想将其转换为2014-01-02 01:14:45
对于最大情况
如果max(ticktime)::TIMESTAMP2014-01-02 01:14:45.5,我想将其转换为2014-01-02 01:14:46

    SELECT DISTINCT ON (1) generate_series ( min(ticktime)::TIMESTAMP, max(ticktime)::TIMESTAMP, '1 second'::interval) AS ticktime
    FROM cffexes

最佳答案

您可以将结果投射到一秒以下,如下所示:

SELECT DISTINCT ON (1) generate_series (
    min(ticktime)::TIMESTAMP(0),
    max(ticktime)::TIMESTAMP(0)+1,
    '1 second'::interval) AS ticktime
FROM cffexes;

如果您需要除秒以外的地板/天花板时间,请使用timestamp
当然,这会留下一个非常小的洞,如果date_trunc()恰好是一个偶数秒,则会产生一个过多的洞。所以你可以尝试一下,但要知道浮点值对等式是出了名的不友好。
SELECT DISTINCT ON (1) generate_series (
    min(ticktime)::TIMESTAMP(0),
    max(ticktime)::TIMESTAMP(0) + (max(ticktime)::TIMESTAMP(0)=max(ticktime)::TIMESTAMP)::int,
    '1 second'::interval) AS ticktime
FROM cffexes;

07-26 01:52