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