Date difference in days in postgresql

Question

I am trying to calculate the difference between dates in days. Datatype is Text for columns snapshot_date and date_opened.I am getting an ERROR: function date_part(unknown, integer) does not exist

SELECT DATE_PART('day', snapshot_date::date -date_opened::date)::number from my_table

Check the similar thread https://stackoverflow.com/questions/17833176/postgresql-days-months-years-between-two-dates — Mariusz Beltowski, Jun 18 '19 at 21:29
"Datatype is Text for columns snapshot_date and date_opened" -- Fix this and use a date type like `date` or `timestamp`. — sticky bit, Jun 18 '19 at 21:29
**Never** store date values in a `text` (or `varchar`) column. Doing that is a really, really bad idea. — , Jun 18 '19 at 23:40

score 0 · Answer 1 · answered Jun 18 '19 at 23:38

As documented in the manual subtracting one date from the other returns an integer representing the number of days, so:

snapshot_date::date - date_opened::date

is all you need.

This assumes that both columns can safely be cast to a date.

Date difference in days in postgresql

1 Answers1