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
Topic revision: r5 - 19 Jul 2007, KennethLavrsen
 
Open2300 - Mysql Patch
Copyright 1999-2014 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
This website only use harmless session cookies. See Cookie Policy for details. By using this website you accept the use of these cookies.