Postgres-将TSTZRANGE分为两列 [英] Postgres - split TSTZRANGE in two columns

问题描述

我使用的是PostgreSQL 9.4
我在名为 timerange 的表中有列，并且想要编写 SELECT 查询，该查询将在两个独立的列 time_start 和 time_end 中返回时间范围。
我试图像数组一样处理它，但是它不起作用：

I'm using PostgreSQL 9.4 I have column in a table named timerange and want to write a SELECT query which will return time range in two separate columns time_start and time_end. I tried to handle this like an array but it doesn't work:

select *, timerange[0] as t_start from schedules;

当前表：

| id |                    timerange                        |  
|----|-----------------------------------------------------|  
| 1  | ["2017-05-05 19:00:00+02","2017-05-05 21:00:00+02") |  
| 2  | ["2017-05-05 19:00:00+02","2017-05-05 21:00:00+02") |  

所需表：

| id |        time_start        |       time_end           | 
|----|--------------------------|--------------------------|
| 1  | "2017-05-05 19:00:00+02" | "2017-05-05 21:00:00+02" |  
| 2  | "2017-05-05 19:00:00+02" | "2017-05-05 21:00:00+02" |  

推荐答案

使用 lower（）和 upper（）。

就像这样：

SELECT lower(tsrng) AS start, upper(tsrng) AS end
FROM (
  SELECT tstzrange('2017-05-05 12:00:05', '2017-05-05 16:00:05', '[)') AS tsrng
) sub;

或者您的示例：

select *, lower(timerange) as t_start, upper(timerange) as t_end from schedules;

这篇关于Postgres-将TSTZRANGE分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！