CODEWITHSUNDEEP
postgresql
ajay
ajay

Reputation: 3

postgreSQL increment number in output

I am extracting three values (server, region, max(date)) from my postgresql> But I want to extract an additional 4th field which should be the numerical addition of 1 to 3rd field. I am unable to use date add function as in the database date field is defined as an integer.

date type in DB

date|integer|not null

tried using cast and date add function

MAX(s.date)::date + cast('1 day' as interval)

Error Received

ERROR:  cannot cast type integer to date

Required output

select server, region, max(alarm_date), next date from table .....

testserver, europe, 20190901, 20190902
testserver2, europe, 20191001, 20191002

next date value should be the addition to alarm_date

Upvotes: 0

Views: 325

Answers (2)

SpeederX
SpeederX

Reputation: 404

First solution that came to my mind:

select (20190901::varchar)::date + 1

Which output 2019-09-02 as type date.

Other solutions can be found here.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246123

To convert an integer like 20190901 to a date, use something like

to_date(CAST(s.date AS text), 'YYYYMMDD')

It is a bad idea to store dates as integers like that. Using the date data type will prevent corrupted data from entering the database, and it will make all operations natural.

Upvotes: 2

Related Questions