subtract dates query help

[DotNetGnat]

Guys,

I am trying to subtract two dates(c.return, c.reserved as shown below) in an older mysql version in which new date functions doesnt work...

so far i have this query working fine...but i was looking for an efficient one...anyone has any suggestions on the efficiency improvement to this below query...

String selectStatement = "select count(*) as total_reservations, sum((((time_to_sec(c.return) - time_to_sec(c.reserved))/60/60) " 
      + " + ((to_days(c.return)-to_days(c.reserved))*9) "
      + " - ((week(c.return)-week(c.reserved))*18) "
      + " - ((year(c.return)-year(c.reserved))*936))) as total_hours";

thanks in advance...

-DNG

 

[TonyGroves]

What sort of calculation is this? If you just wanted to calculate the hours difference between two date-time values, then the most efficient way would probably be to convert each date-time to seconds using UNIX_TIMESTAMP() and divide the difference by 3600.

 

[DotNetGnat]

Tony,

Does Unix_TimeStamp() work in older version of mysql...lower than mysql4.x version...

thanks

-DNG

 

[DotNetGnat]

if that function doesnt exist then what would you think the best and efficient alternative for what i am trying to do...thanks

-DNG

 

[TonyGroves]

As far as I know, UNIX_TIMESTAMP was available from time immemorial. It's such a basic function that it's hard to imagine how MySQL could do without it. The alternative would be something along the lines of what you posted, which as you point out is not particularly efficient. Can you try UNIX_TIMESTAMP() and see what happens?