# Postgresql – Subtracting dates give me error in Postgresql

datetimepostgresql

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')


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: http://www.postgresql.org/docs/current/static/functions-formatting.html

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 hh24:mi:ss.us')