7

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;

Current table:

| 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") |

Desired table:

| 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" |
veich
  • 501
  • 1
  • 6
  • 15

1 Answers1

19

Use lower() and upper().

Like this:

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;

Or your example:

select *, lower(timerange) as t_start, upper(timerange) as t_end from schedules;
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32
  • Works like a charm but I think the timezone is lost because from this timerange `["2017-05-05 14:00:00+02","2017-05-05 15:00:00+02")` I get `t_start` equal `2017-05-05 14:00:00.000000` – veich May 05 '17 at 13:57
  • @veich [The time-zone information is there](http://rextester.com/VUCAL92330). Probably, your client interprets the response & puts it to your local time-zone. – pozs May 05 '17 at 14:23
  • IMO it's better to always save the timezone in the database in UTC – Abdlrahman Saber May 10 '23 at 09:17