Hi!

While stumbling around in the source tree, I noticed that the OTRS schema uses DATE for most date/time-related fields, but there are some (three, it seems) fields called xxx_time_unix of type BIGINT.

Storing the epoch directly seems a lot easier when you have to do things like matching all records more than two days old, because one can just subtract 2*60*60*24 and compare it (I'm guessing this is the reason why it's done this way in web_upload_cache). However, it doesn't have to be that difficult with DATE either:

IIRC there are fucntions in the RDBMS for manipulating dates...

use Date::Calc qw(Add_Delta_Days Today);

my $timelimit = sprintf("%04d-%02d-%02d 00:00:00", Add_Delta_Days(Today(), -2));

# Or, if more precision is needed: use Date::Calc qw(Add_Delta_DHMS Today_and_Now);

$timelimit = sprintf("%04d-%02d-%02d %02d:%02d:%02d", Add_Delta_DHMS(Today_and_Now(), -2, 0, 0, 0));

my $SQL = qq[ DELETE FROM foo_table WHERE create_time < ? ];

(It may be easier to do it even easier using TO_CHAR/TO_DATE.)

Storing date/time in several different formats in the same database seems somewhat unfortunate. One reason for this is that the normal DATE fields are a lot easier to manipulate directly by humans. Another is that if someone should, for example, ever want to move to a timezone-aware datatype, it is a lot easier to migrate old data if it is all stored in the same format. Or am I missing a point here? If so, please tell me. :)

Good point, does anyone know the differences by the supported RDBMS's with regards to dates and times? If there are no differences the date time data types should be as implemented in the database, if there are differences the otrs application should take care of handling dates epoc-style . my two c, /G

Regards, -- Kristoffer.

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev