...
- Finding the elapsed time between two dates - AGE, INTERVAL, simple subtraction
You can use the "AGE" function or date subtraction to find the time elapsed between two dates. In these example, we are using "CURRENT_DATE" as one of the dates, but you can use any two date fields, cast as the same data type.
...
- The Age function will show the results broken down into time components: Example: 10 mons 2 days 07:15:12.524
- The date subtraction function will show the number of days, if the two dates you're subtracting are cast as "DATE": Example: 309
- When the dates you're subtracting are in another date format (such as timestamptz), the results will show time components, which may be useful (or not). Example: 308 days 07:15:12.524
To find the number days elapsed since a given calendar date, use the subtraction function:
current_date -'2022-07-01' as number_of_days_since_7_1_22
To find a calendar date that is some time interval from another date, use the "INTERVAL" function in combination with subtraction (or addition, as needed):
to_char ((current_date - interval '33 years 9 months 26 days')::date,'mm/dd/yyyy') AS date_record_created
...result displays as a date: 03/19/1989 – because that's how we formatted the expression with TO_CHAR
age (current_date,'1989-03-17') AS amount_of_time_in_collection
...result displays as a time interval: 33 years 9 mons 26 days