0

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

Ratan Uday Kumar
  • 5,738
  • 6
  • 35
  • 54
ajay
  • 3
  • 2

2 Answers2

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

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.

SpeederX
  • 329
  • 2
  • 7
  • can i able to get the output without -(hyphens) after adding 1 – ajay Oct 04 '19 at 10:13
  • This is a bad practice, as the other user suggested. Anyway, in order to do so: `select replace((to_date(20190901::text,'YYYYMMDD') + 1)::text,'-','')::integer` This does replace hyphens and converts back to integer. As if i am correct... you want to update the value. You should really change the type and avoid doing so. Risky and very tricky – SpeederX Oct 04 '19 at 10:39