Change of MySQL timestamp fields.

New mysql2300 release

Introduction

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

Discussion and Comments

---

This patch has been integrated into my 1.11 sources

-- KennethLavrsen - 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";

then by

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.

-- OschenLars - 19 Nov 2006

---

Lars - I think I will let this patch stay.

Emiliano has updated also mysqlhistlog in MysqlPatch2

-- KennethLavrsen - 19 Jul 2007

---