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')
  • What is the error you are getting? – Alexandros Jan 26 '14 at 8:44

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.