Elapsed time between 2 DateTime values

[Thomas Bartkus]

MySQL Version 4.0.20 on a Linux server.

How does one get the elapsed time between (2) DateTime values?
I need the answer to the nearest minute.

Is upgrading to Ver 5 with its more robust date/time functions the only
solution?

You can directly subtract 2 DateTime values and a long integer results.
What is that number?

Thanks in advance
Thomas Bartkus

[Aggro]

Thomas Bartkus wrote:

MySQL Version 4.0.20 on a Linux server.

How does one get the elapsed time between (2) DateTime values?
I need the answer to the nearest minute.

Is upgrading to Ver 5 with its more robust date/time functions the only
solution?

You can directly subtract 2 DateTime values and a long integer results.
What is that number?

mysql> select *,date2-date1 from datetest;
+---------------------+---------------------+-------------+
| date1 | date2 | date2-date1 |
+---------------------+---------------------+-------------+
| 2004-06-29 20:26:09 | 2004-06-29 20:28:47 | 238 |
+---------------------+---------------------+-------------+
1 row in set (0.00 sec)
date2-date1 is difference in seconds. If you want to get result in
minutes, you can divide it by 60 (One minute contains 60 seconds).

mysql> select *,((date2-date1)/60) from datetest;
+---------------------+---------------------+--------------------+
| date1 | date2 | ((date2-date1)/60) |
+---------------------+---------------------+--------------------+
| 2004-06-29 20:26:09 | 2004-06-29 20:28:47 | 3.9666666666667 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)

And you can of course round the result

mysql> select *,round((date2-date1)/60) from datetest;
+---------------------+---------------------+-------------------------+
| date1 | date2 | round((date2-date1)/60) |
+---------------------+---------------------+-------------------------+
| 2004-06-29 20:26:09 | 2004-06-29 20:28:47 | 4 |
+---------------------+---------------------+-------------------------+
1 row in set (0.02 sec)

Please read about round() function from the manual:
http://dev.mysql.com/doc/mysql/en/Ma...functions.html

Because you might want to use TRUNCATE() or FLOOR() instead.

[John Doherty]

In article <pa***************************@comcast.net>, Thomas Bartkus
<th***********@comcast.net> wrote:

MySQL Version 4.0.20 on a Linux server.

How does one get the elapsed time between (2) DateTime values?
I need the answer to the nearest minute.

One way is to convert both to unix timestamps, subtract, and then
convert the result back to a datetime:

mysql> select * from foo;
+---------------------+---------------------+
| bar | baz |
+---------------------+---------------------+
| 2004-06-26 00:00:00 | 2004-06-26 01:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select sec_to_time(unix_timestamp(baz) - unix_timestamp(bar))
as elapsed from foo;
+----------+
| elapsed |
+----------+
| 01:00:00 |
+----------+
1 row in set (0.00 sec)

--

[Thomas Bartkus]

"John Doherty" <jd******@nowhere.null.not> wrote in message
news:jd***********************@192.168.2.178...

In article <pa***************************@comcast.net>, Thomas Bartkus
<th***********@comcast.net> wrote:

MySQL Version 4.0.20 on a Linux server.

How does one get the elapsed time between (2) DateTime values?
I need the answer to the nearest minute.

One way is to convert both to unix timestamps, subtract, and then
convert the result back to a datetime:

mysql> select * from foo;
+---------------------+---------------------+
| bar | baz |
+---------------------+---------------------+
| 2004-06-26 00:00:00 | 2004-06-26 01:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select sec_to_time(unix_timestamp(baz) - unix_timestamp(bar))
as elapsed from foo;
+----------+
| elapsed |
+----------+
| 01:00:00 |
+----------+
1 row in set (0.00 sec)

Bingo! That does it.

Thank you so much
Thomas Bartkus

[Thomas Bartkus]

"Aggro" <sp**********@yahoo.com> wrote in message
news:Id***************@read3.inet.fi...

mysql> select *,date2-date1 from datetest;
+---------------------+---------------------+-------------+
| date1 | date2 | date2-date1 |
+---------------------+---------------------+-------------+
| 2004-06-29 20:26:09 | 2004-06-29 20:28:47 | 238 |
+---------------------+---------------------+-------------+
1 row in set (0.00 sec)
date2-date1 is difference in seconds.

In the example you show, the differences between date2 and date1 is 158
seconds. The 238 result might be meaningful, but it sure ain't seconds!

What in heck is MySQL returning when you subtract (2) datetime values?
Thomas Bartkus

[Aggro]

Thomas Bartkus wrote:

In the example you show, the differences between date2 and date1 is 158
seconds. The 238 result might be meaningful, but it sure ain't seconds!

You are correct. I should have checked the results before posting this.
I am very sorry about this.

mysql> select date2+0,date1+0,date2-date1 from datetest;
+----------------+----------------+-------------+
| date2+0 | date1+0 | date2-date1 |
+----------------+----------------+-------------+
| 20040629202847 | 20040629202609 | 238 |
+----------------+----------------+-------------+
1 row in set (0.00 sec)

This should give more information about what the result would be. It is
not actually meaningfull at all. The values of dates in number format
are just formed as "yyyymmddhhmmss". So it contains the same information
as the string, but instead of using characters, each digit is actually
an integer. That format might be usefull for someone, but you can't use
that format to calculate differences.

So like you have already been adviced on the other post, use the
unix_timestamp() function, to convert time into seconds.

[Thomas Bartkus]

----- Original Message -----
From: "Aggro" <sp**********@yahoo.com>
Newsgroups: mailing.database.mysql
Sent: Thursday, July 01, 2004 5:17 PM
Subject: Re: Elapsed time between 2 DateTime values

Thomas Bartkus wrote:

In the example you show, the differences between date2 and date1 is 158
seconds. The 238 result might be meaningful, but it sure ain't seconds!

You are correct. I should have checked the results before posting this.
I am very sorry about this.

Please - please. Don't apologize.
Your help is most appreciated!

I didn't mean to tweak you for making an error. It was only me pumping for
*more* of your valuable info.
And - you picked it apart to shed light on how MySQL handles date
arithmetic.
And - you posted it for all to see!

A thousand thanks.
Thomas Bartkus