Subtracting dates give me error in Postgresql

Question

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

• Alexandros: What is the error you are getting?

Answer 1

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