Mysql INSERT loop
Posted: Mon Oct 25, 2010 4:44 am
Hey, i need some help with the following:
I have a DATETIME range derived from columns "dv_start" and "dv_end".
I want to make a loop in the SQL that keeps adding 15 minutes to "dv_start" while dv_start < dv_end. aka ADDTIME(dv_start, 00:15:00)
The results from adding 15 minutes to dv_start should be used as rows (either inserted in new table or something similar method of achieving it) so i can use COUNT(dv_busy_free) and group it by those results that SQL generates with WHILE;
The code relating to SELECT COUNT(...) FROM ... WHERE ... GROUP BY ... (is working nicely as planed)
but, these are the problems:
- when ever i try adding time it returns NULL (even after casting and juggling around with types, i didnt get reliable method of getting it working - sometimes it does work sometimes it hits me with NULL again) or when i try to use DATEDIFF() - code works nicely with NOW() and 1 column, but when i use both columns... it simply returns NULL => what am i missing here...
- (as i never used PROCEDURE, BEGIN, WHILE, ENDWHILE, END inside mysql) - my understanding and trial/error attempts of that piece of code didnt get anywhere so i guess there is no use posting basic copy/paste syntax from dev.mysql.com.
Table structure:
Table data:
After hitting a wall for hours i could really use some help.
Tnx.
I have a DATETIME range derived from columns "dv_start" and "dv_end".
I want to make a loop in the SQL that keeps adding 15 minutes to "dv_start" while dv_start < dv_end. aka ADDTIME(dv_start, 00:15:00)
The results from adding 15 minutes to dv_start should be used as rows (either inserted in new table or something similar method of achieving it) so i can use COUNT(dv_busy_free) and group it by those results that SQL generates with WHILE;
The code relating to SELECT COUNT(...) FROM ... WHERE ... GROUP BY ... (is working nicely as planed)
but, these are the problems:
- when ever i try adding time it returns NULL (even after casting and juggling around with types, i didnt get reliable method of getting it working - sometimes it does work sometimes it hits me with NULL again) or when i try to use DATEDIFF() - code works nicely with NOW() and 1 column, but when i use both columns... it simply returns NULL => what am i missing here...
- (as i never used PROCEDURE, BEGIN, WHILE, ENDWHILE, END inside mysql) - my understanding and trial/error attempts of that piece of code didnt get anywhere so i guess there is no use posting basic copy/paste syntax from dev.mysql.com.
Table structure:
Code: Select all
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dv_start` datetime DEFAULT NULL,
`dv_end` datetime DEFAULT NULL,
`dv_busy_free` int(11) DEFAULT "0",
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
Code: Select all
id| dv_start| dv_end
_______________________________________________________
1| "0000-00-00 01:01:01"| "0000-00-00 03:03:03"
2| "0000-00-00 01:01:01"| "0000-00-00 01:02:01"
3| "0000-00-00 01:01:01"| "0000-00-00 01:03:01"
4| "0000-00-00 01:01:01"| "0000-00-00 01:04:01"
5| "0000-00-01 01:01:01"| "0000-00-01 01:02:01"
6| "0000-00-01 01:01:01"| "0000-00-01 01:03:01"
7| "1000-00-03 01:01:01"| "0000-00-01 01:04:01"
After hitting a wall for hours i could really use some help.
Tnx.