How to substract minutes from a time field in mysql query? 1

[ejocobip]

Good day people!

I have a problem, i want to substract minutes from a time field in a mysql query, i'm trying with the next query:

UPDATE TblOrders SET TmHours = TmHours - '00:09:00';

In the query I want to substract 9 minutes from all time fields named TmHours in the table, the datatype for the field TmHours is time.

Any help will be welcomed, thanks!

Patty

 

[sleipnir214]

If I have the table date_test:

[tt]+------+---------------------+
| pkID | test |
+------+---------------------+
| 1 | 2006-06-06 06:06:06 |
| 2 | 2006-07-07 07:07:07 |
| 3 | 2006-09-09 09:09:09 |
+------+---------------------+[/tt]

Then the query:

UPDATE date_test SET test = test - INTERVAL 9 MINUTE;

makes the table:

[tt]+------+---------------------+
| pkID | test |
+------+---------------------+
| 1 | 2006-06-06 05:57:06 |
| 2 | 2006-07-07 06:58:07 |
| 3 | 2006-09-09 09:00:09 |
+------+---------------------+[/tt]

Is that what you're looking for?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ejocobip]

Good day sleipnir214,

thanks for your quick reply, I tryed with INTERVAL but it didn't work for me, perhaps is because my column is TIME format and not DATE, for example in the column I have:

+------+---------------------+
| pkID | test |
+------+---------------------+
| 1 | 06:06:06 |
| 2 | 07:07:07 |
| 3 | 09:09:09 |
+------+---------------------+

I want to be able to substract 9 minutes from column test like this:

+------+---------------------+
| pkID | test |
+------+---------------------+
| 1 | 05:57:06 |
| 2 | 06:58:07 |
| 3 | 09:00:09 |
+------+---------------------+

I hope you can help me, I'll really apreciate it.

Have a nice day!

Patty

 

[sleipnir214]

All right, then...

Given a table foo:

[tt]+----+----------+
| Id | test |
+----+----------+
| 1 | 10:10:10 |
| 2 | 11:11:11 |
| 3 | 12:12:12 |
| 4 | 13:13:13 |
| 5 | 04:51:00 |
+----+----------+[/tt]

After the query:

UPDATE foo SET test = SUBTIME(test,'00:09:00');

is run, the table will look like:

[tt]+----+----------+
| Id | test |
+----+----------+
| 1 | 10:01:10 |
| 2 | 11:02:11 |
| 3 | 12:03:12 |
| 4 | 13:04:13 |
| 5 | 14:05:14 |
+----+----------+[/tt]

This method requires a newer version of MySQL. One test machine, running MySQL 3.23, did not have SUBTIME(). Another, running MySQL 4.1, did.



Want the best answers? Ask the best questions! TANSTAAFL!