On 11/07/13 17:17, Huan Ruan wrote:
Hi Guys
We are migrating to Postgres. In the current system, we use datediff()
function to get the difference between two dates, e.g. datediff
(month, cast('2013-01-01' as timestamp), cast('2013-02-02'
as timestamp) returns 1.
I understand that Postgres has Interval data type so I can achieve the
same with Extract(month from Age(date1, date2)). However, I try to
make it so that the existing SQL can run on both databases without
changes. One possible way is to add a datediff function to Postgres,
but the problem is that month/day/year etc is a keyword not a string
like 'month'. I noticed that Postgres seems to convert Extract(month
from current_timestamp) to date_part('month', current_timestamp), you
can also do Extract('month' from current_timestamp). So it seems
internally, Postgres can do the mapping from month to 'month'. I was
wondering if there is a way for me to do the same for the datediff()
function? Any other ideas?
Thanks
Huan
Purely out of curiosity, could you tell us what database software you
are moving from, as well as a rough idea of the size of database, type
and volume of database queries?
It would also be of interest to know what postgres features in
particular were the biggest motivations for change, and any aspects that
gave you cause for concern - obviously overall, it must have come across
as being better .
I strongly suspect that answering these questions will have no direct
bearing on how people will answer your query! :-)
Cheers,
Gavin