shadkeene
11-11-2007, 05:09 PM
Hi,
I posted a question earlier about subtracting values but my problem has grown a bit. It now has a few layers to it unfortunately. Users input data into a form and the passed parameters are stored in mysql tables. I'd like to subtract some of the values through mysql, specifically values that need to represented in time format. So, I tried to subtract the values initially...then I realized I needed to change the datatype from char(4) to TIME in order to get accurate results. Now, my problem has become the fact that I can't submit blank values on the form because the perl cgi script returns: "Could not execute: Incorrect time value: '' for column 'LOCLDTIME' "
The TIME datatype default is NULL, so why shouldn't it work if I don't enter a value?
So, my problems are:
1) I'd like to be able to submit empty values for times into mysql tables when the corresponding event on the form doesn't occur.
2) I'd like to subtract two values from two different tables. For instance, subtract 19:30 of column LOCLDTIME from 22:00 of column RATE45 from the below tables. My tables are arranged with primary key of timestamp now(), and since there are usually just two rows per day, I've been relating and joining the tables based on whether the timestamp has the same data and is prior to 15:00:00 or after 15:00:00. I know it's kinda screwy, but it's what I have right now...just to give you a background. Thanks so much for any help!
Table=shft_summary
date_time | RATE45 |
+---------------------+
| 2007-10-28 13:00:00 |
| 2007-10-29 13:00:00 |
| 2007-10-29 20:00:00 |
| 2007-10-30 20:00:00 |
| 2007-10-31 20:00:00 |
| 2007-11-02 13:00:00 |
| 2007-11-04 13:00:00 |
| 2007-11-04 21:08:34 |
| 2007-11-05 12:20:15 |
| 2007-11-06 13:00:00 | |2200
| 2007-11-06 20:58:01 |
| 2007-11-07 13:00:00 |
+---------------------+
Table=coldseason
+---------------------
| date_time | LOCLDTIME |
+---------------------
| 2007-10-28 08:00:00
| 2007-10-29 08:00:00
| 2007-10-29 16:00:00
| 2007-10-30 16:00:00
| 2007-10-31 16:00:00
| 2007-11-02 08:00:00
| 2007-11-04 09:26:37
| 2007-11-04 21:08:01
| 2007-11-05 07:58:03
| 2007-11-06 07:50:00 |1930
| 2007-11-06 15:21:48
| 2007-11-07 07:50:00
| 2007-11-07 16:31:25
+---------------------
I posted a question earlier about subtracting values but my problem has grown a bit. It now has a few layers to it unfortunately. Users input data into a form and the passed parameters are stored in mysql tables. I'd like to subtract some of the values through mysql, specifically values that need to represented in time format. So, I tried to subtract the values initially...then I realized I needed to change the datatype from char(4) to TIME in order to get accurate results. Now, my problem has become the fact that I can't submit blank values on the form because the perl cgi script returns: "Could not execute: Incorrect time value: '' for column 'LOCLDTIME' "
The TIME datatype default is NULL, so why shouldn't it work if I don't enter a value?
So, my problems are:
1) I'd like to be able to submit empty values for times into mysql tables when the corresponding event on the form doesn't occur.
2) I'd like to subtract two values from two different tables. For instance, subtract 19:30 of column LOCLDTIME from 22:00 of column RATE45 from the below tables. My tables are arranged with primary key of timestamp now(), and since there are usually just two rows per day, I've been relating and joining the tables based on whether the timestamp has the same data and is prior to 15:00:00 or after 15:00:00. I know it's kinda screwy, but it's what I have right now...just to give you a background. Thanks so much for any help!
Table=shft_summary
date_time | RATE45 |
+---------------------+
| 2007-10-28 13:00:00 |
| 2007-10-29 13:00:00 |
| 2007-10-29 20:00:00 |
| 2007-10-30 20:00:00 |
| 2007-10-31 20:00:00 |
| 2007-11-02 13:00:00 |
| 2007-11-04 13:00:00 |
| 2007-11-04 21:08:34 |
| 2007-11-05 12:20:15 |
| 2007-11-06 13:00:00 | |2200
| 2007-11-06 20:58:01 |
| 2007-11-07 13:00:00 |
+---------------------+
Table=coldseason
+---------------------
| date_time | LOCLDTIME |
+---------------------
| 2007-10-28 08:00:00
| 2007-10-29 08:00:00
| 2007-10-29 16:00:00
| 2007-10-30 16:00:00
| 2007-10-31 16:00:00
| 2007-11-02 08:00:00
| 2007-11-04 09:26:37
| 2007-11-04 21:08:01
| 2007-11-05 07:58:03
| 2007-11-06 07:50:00 |1930
| 2007-11-06 15:21:48
| 2007-11-07 07:50:00
| 2007-11-07 16:31:25
+---------------------