PostgreSQL: days/months/years between two dates

PostgreSQL: days/months/years between two dates

Asked on December 27, 2018 in PostgreSQL.
Add Comment


  • 3 Answer(s)
    select age('2010-04-01', '2012-03-05'),
           date_part('year',age('2010-04-01', '2012-03-05')),
           date_part('month',age('2010-04-01', '2012-03-05')),
           date_part('day',age('2010-04-01', '2012-03-05'));
    

    The code above returns the years, month, days between two dates.

    Answered on December 27, 2018.
    Add Comment

    We can use this simple one line code,

    select '2015-01-12'::date - '2015-01-01'::date;
    

    Here, we just subtract the given two dates.It yields:

    ?column?
    ----------
    11
    
    Answered on December 27, 2018.
    Add Comment

    Use the code below, It works fine.

    CREATE OR REPLACE FUNCTION datediff(type VARCHAR, date_from DATE, date_to DATE) RETURNS INTEGER LANGUAGE plpgsql
    AS
    $$
    DECLARE age INTERVAL;
    BEGIN
        age := age(date_to, date_from);
            CASE type
            WHEN 'month' THEN
                RETURN date_part('year', age) * 12 + date_part('month', age);
            WHEN 'year' THEN
                RETURN date_part('year', age);
            ELSE
                RETURN (date_to - date_from)::int;
        END CASE;
    END;
    $$;
    

    The use is,

    /* Get months count between two dates */
    SELECT datediff('month', '2015-02-14', '2016-01-03');
     
    /* Get complete years count between two dates */
    SELECT datediff('year', '2015-02-14', '2016-01-03');
     
    /* Get days count between two dates */
    SELECT datediff('day', '2015-02-14', '2016-01-03');
     
    /* Get months count between specified and current date */
    SELECT datediff('month', '2015-02-14', NOW()::date);
    
    Answered on December 27, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.