720639 - create constants for database timestamp formatting

Status: NEW

(Bugzilla :: Database, enhancement)

Description

[:glob ✱]

from bug 240437:

glob:
> + last_seen_date => {TYPE => 'DATETIME'},

LpSolit:
> > This won't work based on the way you defined last_seen_date(). You cannot
> > assume the format of the date stored in the DB to be of the form
> > "%4d-%02d-%02d 00:00:00". You must use $dbh->sql_date_format() to correctly
> > format the datetime coming from the DB, see e.g. Bugzilla::Bug::DB_COLUMNS
> > to see how it's done with creation_ts and delta_ts.

glob:
> + $dbh->sql_date_format('last_seen_date', '%Y-%m-%d') . ' AS last_seen_date',

mkanat:
> WHY ARE YOU FORMATTING THINGS INSIDE OF OBJECTS? Please stop doing this. It
> makes it impossible to use this date in any other fashion! Formatting is for
> UIs, not for backends.

[3 minutes later]

mkanat:
>   Ahh, I see why you're doing this. If you're going to do this, you should
> have a date-formatting constant, and that constant should always format down
> to seconds.
> 
>   If we really did do this consistently across Bugzilla, we could support
> databases that have non-standard date formats, which could be nice.

Comment 1

[David Lawrence [:dkl]]

(In reply to Byron Jones ‹:glob› from comment #0)
> >   Ahh, I see why you're doing this. If you're going to do this, you should
> > have a date-formatting constant, and that constant should always format down
> > to seconds.
> > 
> >   If we really did do this consistently across Bugzilla, we could support
> > databases that have non-standard date formats, which could be nice.

One thing to keep in mind is that their is inconsistency in other areas that use date format strings. For example:

SQL = '%Y.%m.%d %H:%i:%s'
$dbh->sql_date_format('creation_date', '%Y.%m.%d %H:%i:%s') . ' AS creation_date',

DateTime = '%Y.%m.%d %H:%M:%S'
$self->{'modification_date'} = format_time($timestamp, '%Y.%m.%d %H:%M:%S');

So you would need different constants depending on how the format string will be used.

dkl