How to extract day from duration

ravichandra

i have a column of data type 'Interval day to second(25)' which is having sample value
'0 00:04:41.470157' from this i just want to extract 'day' .
could you please help me how can i achieve this

Jim_Knicely

Try this:

dbadmin=> SELECT extract(day from '0 00:04:41.470157'::interval day to second) AS day;
 day
-----
   0
(1 row)

marcothesane

Do you mean this?

WITH input(intv) AS (
          SELECT INTERVAL '0 00:04:41.470157'
UNION ALL SELECT INTERVAL '1 00:04:41.470157'
UNION ALL SELECT INTERVAL '2 00:04:41.470157'
)
SELECT
  intv
, DAY(intv) AS day_extracted
FROM input;

result:

intv             |day_extracted
00:04:41.470157  |            0
1 00:04:41.470157|            1
2 00:04:41.470157|            2

Happy playing, and good luck
Marco