By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Subtracting dates gives me what?

P: n/a
When I subtract dates, I can't figure out what I get out. I first I
thought it was in seconds, but that's not it. Then I figured maybe
HHMMSS, but that does not seem to be it too.

How can I subtract two dates and get the resualt in HH:MM:SS format?

Thanks for any help.
-Eddie
mysql> SELECT name, party, NOW(), indate, NOW()-indate, TIME_FORMAT((NOW()-indate), '%H:%i:%s')
from greeter;
+-----------+-------+---------------------+---------------------+--------------+-----------------------------------------+
| name | party | NOW() | indate | NOW()-indate | TIME_FORMAT((NOW()-indate), '%H:%i:%s') |
+-----------+-------+---------------------+---------------------+--------------+-----------------------------------------+
| Jim | 8 | 2005-09-17 22:40:59 | 2005-09-17 19:56:02 | 28457 | NULL |
| Hello | 9 | 2005-09-17 22:40:59 | 2005-09-17 19:56:11 | 28448 | NULL |
| Corrine | 6 | 2005-09-17 22:40:59 | 2005-09-17 19:56:34 | 28425 | NULL |
| Eddie | 6 | 2005-09-17 22:40:59 | 2005-09-17 19:56:49 | 28410 | NULL |
| Ruby | 1 | 2005-09-17 22:40:59 | 2005-09-17 19:57:15 | 28344 | NULL |
| Kevin | 1 | 2005-09-17 22:40:59 | 2005-09-17 19:57:41 | 28318 | NULL |
| Hello | 1 | 2005-09-17 22:40:59 | 2005-09-17 19:59:17 | 28142 | NULL |
| Hi | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:00:16 | 24043 | 02:40:43 |
| Suse | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:01:10 | 23949 | 02:39:49 |
| Gil gil | 6 | 2005-09-17 22:40:59 | 2005-09-17 20:02:05 | 23854 | 02:38:54 |
| Kitty cat | 4 | 2005-09-17 22:40:59 | 2005-09-17 20:02:40 | 23819 | 02:38:19 |
| Tommis | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:05:02 | 23557 | 02:35:57 |
| T | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:06:13 | 23446 | 02:34:46 |
| Hi | 6 | 2005-09-17 22:40:59 | 2005-09-17 20:33:21 | 20738 | 02:07:38 |
| Bob | 4 | 2005-09-17 22:40:59 | 2005-09-17 20:34:22 | 20637 | 02:06:37 |
| Bob | 4 | 2005-09-17 22:40:59 | 2005-09-17 20:40:19 | 20040 | 02:00:40 |
| Johnny | 3 | 2005-09-17 22:40:59 | 2005-09-17 20:46:59 | 19400 | NULL |
| Corrine | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:48:23 | 19236 | NULL |
| Yoda | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:48:38 | 19221 | NULL |
| Eddie | 4 | 2005-09-17 22:40:59 | 2005-09-17 20:55:09 | 18550 | NULL |
| Lucky | 1 | 2005-09-17 22:40:59 | 2005-09-17 20:57:02 | 18357 | NULL |
| Moe | 5 | 2005-09-17 22:40:59 | 2005-09-17 20:58:15 | 18244 | NULL |
| Jonny | 8 | 2005-09-17 22:40:59 | 2005-09-17 20:59:17 | 18142 | NULL |
| Sam | 3 | 2005-09-17 22:40:59 | 2005-09-17 20:59:35 | 18124 | NULL |
| Bob | 4 | 2005-09-17 22:40:59 | 2005-09-17 21:39:30 | 10129 | 01:01:29 |
+-----------+-------+---------------------+---------------------+--------------+-----------------------------------------+
25 rows in set (0.00 sec)

mysql> describe greeter;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| name | varchar(20) | YES | | NULL | |
| party | decimal(3,0) | YES | | NULL | |
| indate | datetime | YES | | NULL | |
| outdate | datetime | YES | | NULL | |
| id | mediumint(9) | | PRI | NULL | auto_increment |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Sep 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Eddie wrote:
When I subtract dates, I can't figure out what I get out. I first I
thought it was in seconds, but that's not it. Then I figured maybe
HHMMSS, but that does not seem to be it too.

How can I subtract two dates and get the resualt in HH:MM:SS format?


Did you notice that there is a whole page about date and time functions
in the manual:
http://dev.mysql.com/doc/mysql/en/da...functions.html
Sep 18 '05 #2

P: n/a
On Sun, 18 Sep 2005 00:01:41 -0700, Aggro wrote:
Eddie wrote:
When I subtract dates, I can't figure out what I get out. I first I
thought it was in seconds, but that's not it. Then I figured maybe
HHMMSS, but that does not seem to be it too.

How can I subtract two dates and get the resualt in HH:MM:SS format?


Did you notice that there is a whole page about date and time functions
in the manual:
http://dev.mysql.com/doc/mysql/en/da...functions.html

Ya, I have been over that page a lot.
I just discover this in the user comments section.

sec_to_time(unix_timestamp(EndDateTime)-unix_timestamp(StartDateTime))

It seems a long about way, but it gets the number I need.

Thanks
Sep 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.