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