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

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

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Here is what I think is happening. I don't think it is a bug.

You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR
Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

Sql-server – SQL Query with multiple conditions on date

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and 
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)

Best Answer

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Sql-server – SQL Query with multiple conditions on date

Related Question