Postgresql interval to Integer

Question

Currently I have a column call days and the values are 1 day, 2 days etc. I am trying to extract or keep only the numeric value.

Something like:

select CAST(LEFT(days, CHARINDEX(' ', days)) as integer) as #days from daily_table

user330315 · Accepted Answer · 2022-06-21 20:52:02Z

2

You could first cast the string to an interval, then use extract():

select extract(day from days::interval) as "#days"
from daily_table;

answered Jun 21, 2022 at 20:52

user330315

Sign up to request clarification or add additional context in comments.

Comments

Happs · Accepted Answer · 2022-06-21 21:09:27Z

0

You can use substring() function to get the numeric value and then cast to INTEGER

select cast(substring(days, 1,  position('d' in days)-2) as INTEGER)
as "#days" from daily_table;

answered Jun 21, 2022 at 21:09

Happs

1258 bronze badges