Postgresql – Subtracting dates give me error in Postgresql


I use age to get the interval between to date fields. Their type is timestamp.

My code is as below:

select age(timestamp MySecondDateField, timestamp MyFirstDateField)

The data in my field contains:

2014-01-26 09:53:17.036359

How should I subtract two dates?

When I use age as below it works:

select age(timestamp '2001-04-10', timestamp '1957-06-13')

Best Answer

If those columns are timestamp (or date) columns, just use

age(MySecondDateField, MyFirstDateField)

The timestamp or date prefix is only needed for date/timestamp constants.

If those columns are varchar then you should change them to a proper timestamp/date datatype.

Until you do that, you can use the to_date() or to_timestamp() function to convert a string to a date, e.g:

to_timestamp(MySecondDateField, 'yyyy-mm-dd hh24:mi:ss')

For more details on those functions, please see the manual:

You should never store dates (or numbers) as varchar/text columns. To fix your table, you can use a conversion function when you run the alter table:

alter table wrong_design 
   alter MyFirstDateField type timestamp 
   using to_timestamp(some_col, 'yyyy-mm-dd')