3

I was trying to write the my own timestampdiff(DAY, first_date ,second_date) function which will work same as MySql tumestampdiff function.

But i am unable to under stand what would be data type of first argument so that it can accept key work word day , month or year.

Need expert comment as what kind of data type is this in function argument?

Secondly where can i see the mysql inbuilt functions implementation.

ok To add more clarity:

I have a query select * from application a where timestampdiff(DAY,a.created_date, CURRENT_TIMESTAMP(3)) >= 30;

i want above query should run in both oracle and mysql.

Hope u got my point. I thought of writing same function in oracle so that i don't have to do any code change and it will be supported for both database.

Now in above i am unable to figure if i am going to write the function the what should be first parameter type.

Thanks

Zuned Ahmed
  • 1,357
  • 6
  • 29
  • 56
  • 1
    You are aware that you can subtract two timestamps directly? E.g `first_date - second_date`? Depending on the datatype it will either give you an `INTERVAL` (if both are `TIMESTAMP`s) or a `NUMBER` (if both are `DATE`s. –  Oct 26 '12 at 06:29
  • @a_horse_with_no_name, actually subtracting 2 dates doesn't result in a "regular" number datatype- http://stackoverflow.com/a/9346340/1083652 (just as a side note) – A.B.Cade Oct 26 '12 at 09:14
  • @A.B.Cade: interesting reading thank. But for someone just "using" the result it is still an integer (the JDBC driver for example returns such a column with the datatype NUMBER) –  Oct 26 '12 at 09:52
  • See also http://stackoverflow.com/questions/450581/how-to-average-time-intervals – Vadzim Oct 17 '13 at 14:23

1 Answers1

0

I don't think that you need to write your own timestampdiff function since oracle already has one: EXTRACT

EXTRACT(DAY from (first_date - second_date))

If for some reason, you do want to do that, then I don't think there is a datatype for the "datefield" keyword (YEAR, MONTH, DAY, etc).
But since it's a finite set, you can just get a VARCHAR2 and have some if statements in your code...

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • reason is want same function should run in both database mysql and oracle. I know the alternative solution. I am not looking for alternative solution just want iff i want to write the timestampdiff then what should be type of first argument. – Zuned Ahmed Oct 26 '12 at 11:09
  • @ZunedAhmed, I see. I'm not sure this can be done (and can't check this now) but perhaps you can create a package with global variables for `DAY`, `MONTH`, etc - they can be numbers, then create a `synonym` DAY for the day global variable MONTH for the month global variable and so on. In you function have the first param be a number and send one of the synonyms – A.B.Cade Oct 26 '12 at 11:50