• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

How do you compare two unix date mysql columns in a query?

I have my query set that displays the columns/data that i want from multiple tables

two of the columns that are displayed are

"test_time"  and "test_time2"

those time columns are in UNIX time

now i would like to compare those two columns... test_time is always going to be older in time than test_time2.

two requests:

have a column added to the query that displays the time difference.

Then i would like to display or get a row count of how many of those comparisons are with in 24 hour difference.

for an added extra bonus.. how can i display a percentage of those are within 24 hours.

Any help would be awesome!! i love EE!!
0
jporter80
Asked:
jporter80
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
The number of seconds in 24 hours is 86400.  You can check to see if the difference is less than that and/or you can keep a count of the total and a count of the ones that are less than 86400.  Then divide the 'less' total by the overall total and multiply by 100 to get the percentage.
0
 
jporter80Author Commented:
i think i figure out now how to get the percentage and count... now i just need help adding/creating a column to the query called "time_diff" and display the time difference of the other two columns.

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now