CODEWITHSUNDEEP
sqlpostgresqlconcatenationgenerate-series
Khachatur Saribekyan
Khachatur Saribekyan

Reputation: 97

psql generate_series method doesn't support concat inside

The following sql code worked as expected

generate_series('2018-06-29 00:00:00','2018-06-29 23:00:00', interval '1 hour')

but when I put concat method instead of first 2 parameters it's rise an error message

generate_series(concat('2018-06-29 00:00:00', '+05'), concat('2018-06-29 23:00:00', '+05'), interval '1 hour')

The error message 

function generate_series(text, text, interval) does not exist

Upvotes: 0

Views: 914

Answers (3)

Sabari
Sabari

Reputation: 244

If you concat it becomes as text data type. Hence you cannot generate series.

Below query will produce desired result

No need to write "interval". Since start and end are timestamp postgresql understands 5h and 1h are 5hours and 1hour

 select 
 generate_series(timestamp '2018-06-29 00:00:00' + '5h',
                 timestamp '2018-06-29 23:00:00' + '5h', 
                '1h')

Upvotes: 1

user330315
user330315

Reputation:

No need for concat() or string operations.

If you want to add 5 hours to the start and ending timestamp, then just add that:

generate_series(timestamp '2018-06-29 00:00:00' + interval '5 hour',
                timestamp '2018-06-29 23:00:00' + interval '5 hour', interval '1 hour')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

That is because generate_series() does not operate on strings. Convert to the right data type:

select generate_series(concat('2018-06-29 00:00:00'::text, '+05'::text)::timestamp,
                       concat('2018-06-29 23:00:00'::text, '+05'::text)::timestamp,
                       interval '1 hour'
                      )

Upvotes: 1

Related Questions