Change of MySQL timestamp fields.
New mysql2300 release
This Patch is a little enhancement of mysql2300 program. It removes 2 redundant fields and
let you to perform custom query more easily.
Description of Patch
This patch modifies the old schema a little bit. Now there is only one field that stores date and time
of the record and its type is 'datetime', which let you to perform custom query more easily.
This field is filled by the mysql funcion NOW().
These SQL commands let you to move from old to new table schema without losing data:
ALTER TABLE `open2300`.`weather` MODIFY COLUMN `timestamp` DATETIME DEFAULT '0000-00-00 00:00:00';
ALTER TABLE `open2300`.`weather` DROP COLUMN `rec_date`;
ALTER TABLE `open2300`.`weather` DROP COLUMN `rec_time`;
ALTER TABLE `open2300`.`weather` CHANGE COLUMN `timestamp` `datetime` DATETIME NOT NULL DEFAULT 0;
ALTER TABLE `open2300`.`weather` CHANGE COLUMN `windspeed` `wind_speed` DECIMAL(3,1) NOT NULL DEFAULT 0;
Installation of Patch
Change History of Patch
This patch has been integrated into my 1.11 sources
- 19 Jul 2006
I am not that happy with this patch. I agree the fields date time are redundant. It takes some time store them and some space to keep them. The proposed normalized data structure is in general ok but might cause so performance issues later.
It is much fast to select data by entering
SELECT * FROM WEATHER WHERE rec_date = "2006-01-01";
SELECT * FROM WEATHER WHERE timestamp like "2006-01-01%";
Also keep in mind if you what to get the maximum per day
SELECT MAX(temp_out) FROM WEATHER GROUP BY rec_date;
is definitely faster then
SELECT MAX(temp_out) FROM WEATHER GROUP BY LEFT(timestamp,10);
I vote for rejecting this patch.
ATTENTION: If the community votes for this patch mysqlhistlog needs to be changed as well!
@Kenneth: Please inform me before releasing 1.11 so can fix mysqlhistlog to catch up with the new structure.
- 19 Nov 2006
Lars - I think I will let this patch stay.
Emiliano has updated also mysqlhistlog in MysqlPatch2
- 19 Jul 2007