Question 89001 in PostgreSQL Question posted in
The official documentation can be found here.

How to subtract dates in postgres sql with inclusiveness in both dates – Postgresql

I am trying to subtract two dates in postgres sql. example 2022-10-31 and 2022-11-1. So I did below approach.

select (date_column1 - date_column_2) as date_diff
from table;

My expected output is 2 days (data type of column is interval). But I’m only seeing 1 day as output.

Tags:

3

Answers


  1. 0

    The difference is 1 day, so if you need 2 as answer simply add 1 to the result

    select ('2023-11-01':: Date - '2023-10-31' ::date) +1 as date_diff
;
    date_diff
    2

    fiddle

    Login or Signup to reply.
  2. 0

    What’s the difference between 42 and 43?. It’s one .

    But, of course, 42 and 43 are two adjacent numbers.

    It’s the old question of the fence posts and the distance between them: If the fence consists of two posts and the distance between two posts is one meter, the fence is one meter long.

    Login or Signup to reply.
  3. 0

    You are effectively doing:

    select '11/01/2022 00:00:00'::timestamptz - '10/31/2022 00:00:00'::timestamptz;
 ?column? 
----------
 1 day

    In other words Midnight to Midnight which is generally a 24 hour period, with exceptions below.

    You can see that best across a Day light savings time change. For here(USA) that occurred on March 12, 2023 and November 5, 2023 so:

    --Day before change

select '03/12/2023 00:00:00'::timestamptz - '03/11/2023 00:00:00'::timestamptz;
 ?column? 
----------
 1 day

--Day of change to DST

select '03/13/2023 00:00:00'::timestamptz - '03/12/2023 00:00:00'::timestamptz;
 ?column? 
----------
 23:00:00

--Day of change back to standard time

select '11/06/2023 00:00:00'::timestamptz - '11/05/2023 00:00:00'::timestamptz;
    ?column?    
----------------
 1 day 01:00:00
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search