How to extract day from duration

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

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2018

    Try this:

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

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file