如何将时间戳从一个范围扩展到另一个范围

Question

我有一个带有项目更改时间戳的表：

• ID:INT
• 项：INT-外键
• 创建：TIMESTAMP

我需要某个项目将创建的时间戳压缩到某个范围。Ie.:

• src范围：2015年10月1日00:00-2015年4月4日23:59
• dst范围：2015年10月1日00:00-2015年10日23:59
• 因此，示例TIMESTAMP看起来像：
  • 2015年2月10日00:00->2015年1月1日6:00
  • 2015年3月3日00:00->2015年1月1日12:00
  • 2015年4月10日00:00->2015年1月1日18:00
  • 2015年4月10日12:00->2015年1月1日21:00

我需要保留年、月、日、小时、分钟、秒作为时间戳的一部分。不得将秒/分钟四舍五入为零。

缩放精度无关紧要，但创建的时间戳的更改顺序必须与缩放前保持一致。我不太关心其他缩放细节：是开始/最终时间、包含/排除时间，还是缩放到6:0/5:59小时的范围。

我可以使用一些外部应用程序来完成这项工作，这些应用程序会自行转换它，然后更新时间戳。但是，我现在只需要使用SQL来完成它。有可能吗？它可能是进步后特有的。

您可能会认为，应用缩放后不会发生冲突。

Answer 1

这样的东西？

select id, item, (' ['||created||', '||created_l- '00:00.000001'::time||') ' )::tsrange as some_range from (
select id, item, created  , lead(created) over(order by created)  created_l from my_table  order by created) q1

Answer 2

with r (src, dst) as ( values
    (
        tsrange('2015-10-01', '2015-10-05', '[)'),
        tsrange('2015-10-01', '2015-10-02', '[)')
    ),
    (
        tsrange('2015-10-06', '2015-10-10', '[)'),
        tsrange('2015-10-02', '2015-10-03', '[)')
    )
), t (id, src) as ( values
    (1,'2015-10-02 00:00'::timestamp),
    (2,'2015-10-03 00:00'),
    (3,'2015-10-04 00:00'),
    (4,'2015-10-04 12:00'),
    (5,'2015-10-06 11:00'),
    (6,'2015-10-07 15:00')
)
select
    t.id, t.src,
    lower(r.dst) + rank() over(
            partition by r.dst order by t.src
    ) * interval '1 second' as squeezed
from
    t
    inner join
    r on t.src <@ r.src
;
 id |         src         |      squeezed       
----+---------------------+---------------------
  1 | 2015-10-02 00:00:00 | 2015-10-01 00:00:01
  2 | 2015-10-03 00:00:00 | 2015-10-01 00:00:02
  3 | 2015-10-04 00:00:00 | 2015-10-01 00:00:03
  4 | 2015-10-04 12:00:00 | 2015-10-01 00:00:04
  5 | 2015-10-06 11:00:00 | 2015-10-02 00:00:01
  6 | 2015-10-07 15:00:00 | 2015-10-02 00:00:02