Calculating duration between two dates

Question

I am using Views Calc for Drupal 7 to calculate duration. I have some questions:

If use Date (Unix timestamp) and subtract end date from start date of my date field with time it gives me seconds then I divide it with 86400 (the seconds in a day) it gives me number of days.

My code in Views Calc:

( %field_data_field_we_from_date.field_we_from_date_value2 -
  %field_data_field_we_from_date.field_we_from_date_value ) / 86400

which is great;

however:

1. If I use date (not UNIX time stamp) and with no time only year, month, and day then it gives me a number which I don't understand what it represents. If I divide it with 86400, the answer does not look correct. My problem is that we don't need to add time in the field. The end-user doesn't like to see time field with date.

2. When I try to use NOW() it give error in Views Calc setting which says NOW value is not allowed here! I need NOW() - my_date_field to get age and other duration which do not have end date field.

3. ROUND() function also doesn't seem to be working.

Any help would be highly appreciated.

Answer 1

You can do a little tweak.

After you get the only date in YYYY-MM-DD string format, concate 00:00:00 as time to the string. then you can get unix time format by using function strtotime() that takes a string date format and returns an integer of time format (unix).

Hopefully this helps you getting the correct duration.