Difference between TIMESTAMP columns

Anon

Hi,

In one of my tables there are two columns with data of TIMESTAMP(14) type. Now I need to make a query to get the AVERAGE, MINIMUM and MAXIMUM of the difference between them. That's OK. But it returns a number (I don't even know what exactly that is, the number of seconds, the number of seconds from a certain point of time or what). What I really want is to display the data formatted to the conventional number_of_days, hh:mm:ss format. Is it possible? Has anyone done anything similar? Please help me!!!

Thanks, Akos

Anon

It's a unix timestamp. That is the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

It's a standard format, so wide used that
almost all the functions of Date and
time manipulation deal with it.

You can do all your operations on it,
but when You have to display the result,
You have to put it in human readable form
using date() function for example.

For further information about date and
time manipulation functions in php, che
the appendix X on the manual.

Regards

Anon

Thank you Franco,

What you wrote is pretty useful but I describe my problem in detail. I have a table with two TIMESTAMP columns. I need to make a query to get the MAXIMUM difference IN TIME between the two. That means I should get a result something like 2 days 1 hour 27 min 12 sec.

I checked manually and the biggest difference in the table between the two columns is 1 min 5 sec, which is equal with 65 sec.

But my query (SELECT MAX(column1-column2) AS max_diff FROM table) returns 145, which is absolutely nonsense. Do you know what I mean? If I get the number 65 back, there is no problem, I can get the real difference in time between the two. But now I get either 1 min 45 sec, or something similar.

Or if you can give me an idea how to combine this query with DATE() or other functions, I'd be grateful.

I can't believe that this is a unique problem in the world! Some of you must have solved it earlier.

I hope that you can help me. Thanks again. I appreciate it.

Thanks, Akos

Anon

which db are you using?
Mysql?

Anon

If You use mysql, You should try whit
its date and time functions described
at chap 7.4.11 of Mysql manual, for ex:

SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col)

You can find the docs at:
http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_functions

Ciao!

Anon

MySQL doesn't use unix_timestamps. Read the Manual.

Here's your solution Akos:

SELECT SEC_TO_TIME(TIME_TO_SEC(NOW()) - TIME_TO_SEC(your_timestamp_column)) FROM your_table

It'll return results like this:
00:03:30
00:02:12

Adapt to your needs.

---John Holmes...