Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE db(id int, timelist time[]);
INSERT INTO db VALUES
  (0, NULL)
, (1, '{}')
, (2, '{11:00:00,12:00:00}')
, (3, '{12:12, 01:01:01.123456, 23:59:59.999999}');

TABLE db;
4 rows affected



id
timelist


0
null

1
{}

2
{11:00:00,12:00:00}

3
{12:12:00,01:01:01.123456,23:59:59.999999}


-- casting to varchar(5) truncates
SELECT id, timelist::varchar(5)[] AS t_varchar5_arr FROM db;


id
t_varchar5_arr


0
null

1
{}

2
{11:00,12:00}

3
{12:12,01:01,23:59}


-- then cast to more generic type text[] or even text if you prefer
-- or even cast back get truncated time values:
SELECT id
     , timelist::varchar(5)[]::text[] AS t_text_arr
     , timelist::varchar(5)[]::text   AS t_text
     , timelist::varchar(5)[]::time[] AS t_times
FROM   db;


id
t_text_arr
t_text
t_times


0
null
null
null

1
{}
{}
{}

2
{11:00,12:00}
{11:00,12:00}
{11:00:00,12:00:00}

3
{12:12,01:01,23:59}
{12:12,01:01,23:59}
{12:12:00,01:01:00,23:59:00}


-- to format any way you want - but note that NULL is transformed to empty array
SELECT *
FROM   db
     , LATERAL (SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))) x(times_formatted);


id
timelist
times_formatted


0
null
{}

1
{}
{}

2
{11:00:00,12:00:00}
{11:00,12:00}

3
{12:12:00,01:01:01.123456,23:59:59.999999}
{12:12,01:01,23:59}


-- done properly:
SELECT *
FROM   db
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))
   ) x(times_formatted) ON db.timelist IS NOT NULL;


id
timelist
times_formatted


0
null
null

1
{}
{}

2
{11:00:00,12:00:00}
{11:00,12:00}

3
{12:12:00,01:01:01.123456,23:59:59.999999}
{12:12,01:01,23:59}


-- CAREFUL! CROSS JOIN and outer aggregation eliminates rows with NULL or empty array!
SELECT id, array_agg(to_char(tl.tle, 'HH24:MI') ORDER BY tl.n)
FROM   db
CROSS  JOIN LATERAL unnest(db.timelist) WITH ORDINALITY AS tl(tle, n)
GROUP  BY id;


id
array_agg


2
{11:00,12:00}

3
{12:12,01:01,23:59}


-- LEFT JOIN fixes this, but still note how NULL and {} are transformed
SELECT id, array_agg(to_char(tl.tle, 'HH24:MI') ORDER BY tl.n)
FROM   db
LEFT   JOIN LATERAL unnest(db.timelist) WITH ORDINALITY AS tl(tle, n) ON true
GROUP  BY id;


id
array_agg


0
{NULL}

1
{NULL}

2
{11:00,12:00}

3
{12:12,01:01,23:59}

id	timelist
0	null
1	{}
2	{11:00:00,12:00:00}
3	{12:12:00,01:01:01.123456,23:59:59.999999}

id	timelist	times_formatted
0	null	{}
1	{}	{}
2	{11:00:00,12:00:00}	{11:00,12:00}
3	{12:12:00,01:01:01.123456,23:59:59.999999}	{12:12,01:01,23:59}