I'm trying to calculate days between two dates.
Let's say I have the date '2005-05-25 06:04:08' and I want to count how many days are there between that day and the current date.
I tried doing:
SELECT DATE_PART('day', AGE(CURRENT_TIMESTAMP, '2005-05-25 06:04:08'::timestamp )) AS days; But for some reason this returns 11, it's not taking into account the difference in years. How can I solve this?
The AGE() function may be causing the issue, as the correct use of DATE_PART() would look like this:
SELECT DATE_PART('day', CURRENT_TIMESTAMP - '2005-05-25 06:04:08'::timestamp) AS days; Note that this would return the number of full days between the current time stamp and the provided start. So the difference between 2021-01-01 02:30:00 and 2021-01-02 00:00:00 would be 0, as no full day had passed. If you need to count days from midnight, then ypercube™’s solution is the way to go 👍🏻
SELECT CURRENT_DATE - '2005-05-25 06:04:08'::date AS days ; 
