From: Patrick (patrickcoconet.com)
Date: Thu Mar 03 2005 - 19:16:21 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

The return you are getting is correct for the format you are using. A 90
second difference is in fact 1 minute, 30 seconds(130).
To get the time difference in seconds convert the datetime or timestamp to a
julian date or unixtime and then process.

  SELECT start_time, end_time, UNIX_TIMESTAMP(end_time) -
UNIX_TIMESTAMP(start_time) FROM mailings_sendstats order by start_time
desc;

I hope this helps...

Pat...

CocoNet Corporation
SW Florida's 1st ISP

----- Original Message -----
From: "Brian Erickson" <erickson.briangmail.com>
To: <mysqllists.mysql.com>
Sent: Thursday, March 03, 2005 7:23 PM
Subject: # Seconds between two datetime fields

> We have a table that has two datetime fields (start_time and
> end_time). We'd like to be able to calculate the number of seconds
> between these two fields in a query. However, a simple "end_time -
> start_time" does not yield a correct result.
>
> SELECT start_time, end_time, end_time - start_time FROM
> mailings_sendstats order by start_time desc;
> +---------------------+---------------------+-----------------------+
> | start_time | end_time | end_time - start_time |
> +---------------------+---------------------+-----------------------+
> | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 |
> | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 |
> | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 |
> | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 |
> | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 |
> | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 |
> | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 |
> | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 |
> | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 |
> | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 |
> | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 |
> | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 |
> etc
>
> As you can see, if the time difference is less than 1 minute, a
> correct result is returned. If the difference is 1 minute and 30
> seconds, '130' is returned instead of 90.
>
> I tried playing with the end_time - start_time conversion using
> different calculations. What I came up with is below.
>
> mysql> SELECT start_time, end_time,
> -> end_time - start_time as cur,
> -> FLOOR((end_time - start_time) / 100) as num,
> -> (end_time - start_time) % 100 as mod,
> -> (FLOOR((end_time - start_time) / 100) * 60) + (end_time -
> start_time) % 100 AS seconds
> -> FROM mailings_sendstats
> -> ORDER BY id desc;
>
+---------------------+---------------------+-----+------+------+---------+
> | start_time | end_time | cur | num | mod | seconds
|
>
+---------------------+---------------------+-----+------+------+---------+
> | 2005-03-02 19:10:00 | 2005-03-02 19:11:07 | 107 | 1 | 7 | 67
|
> | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 | 0 | 53 | 53
|
> | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 | 1 | 31 | 91
|
> | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 | 0 | 55 | 55
|
> | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 | 0 | 53 | 53
|
> | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 | 1 | 10 | 70
|
> | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | 1 | 80 | 140
|
> | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 | 1 | 10 | 70
|
> | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 | 1 | 20 | 80
|
> | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 | 1 | 22 | 82
|
> | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 | 1 | 26 | 86
|
> | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 | 0 | 58 | 58
|
> | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 | 1 | 28 | 88
|
> etc
>
> First, I calculated the number of minutes (num), then seconds (mod),
> then total seconds (seconds). If you scroll through the results,
> you'll see most of them are accurate. However, when the beginning time
> is just before a new minute, the entire calculation is thrown off.
> Examples:
>
> | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | 1 | 80 | 140
|
> | 2005-03-02 18:37:59 | 2005-03-02 18:38:53 | 94 | 0 | 94 | 94
|
>
> So my question is, how can I fix this? I know there are several
> date/time functions that I could probably use, but they were not
> introduced until version 4.1 and I am stuck with version 3.23. We also
> modified the table to use timestamp(14) fields instead of datetime
> fields, but the same result occurs.
>
> Ultimately:
>
> a) Can someone think of a way to modify the query above so that it
produces
> *correct* results every time (maybe I'm missing something simple after
> working on this for so long)
>
> b) We're using this table to track execution time of PHP Cron scripts.
> We may be approaching this entirely the wrong way. If someone has
> other ideas, please let me know.
>
> Thank you very much!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=patrickcoconet.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]