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:
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. :)
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
