[thelist] storing dates in MySQL

[Matt Warden]

On Tue, 10 Aug 2004 14:40:50 -0400, Theodore Serbinski
<tserbinski at washsq.com> wrote:
> one idea i had was to have the dateStart reference another table where
> the first row would be now() and each subsequent row would have a valid
> 2 week interval dateStart, ensuring that everyone's timesheet had the
> same dateStart. if this is a good idea, is there an easy way to generate
> these 2 week intervals from the now() row? i'd only want to generate the
> next interval when needed.
> 
> if not, anyone have any good ideas for this? how much to put in the
> database and how much to put in the application side?

This might be a case where you'd want to normalize that part of your
design. Relational databases don't handle temporal data very well at
all ( google://"temporal database" ), so it might be best to minimize
the amount of manipulation you need to do with it.

You'll have to excuse my lack of use of a monospaced font:

INTERVAL
--------------------------------------------
id                         int
datestart             datetime
dateend              datetime


TIMECARD
--------------------------------------------
id                         int
interval_id            int
...


An alternative design would get rid of dateend and instead have an
length (as in interval length) field. Depending on your setup, you
might not even need dateend/length.

Anyways, determining new interval start dates is now trivial, as is
making sure that everyone's timecard begins on the same date. (Note,
however, that if you needed to support, say, two sets of intervals --
e.g. one that started on the tuesday of every week and another that
started on the wednesday --you could still support that with this
design)

In addition. depending on the programming language you are using,
there are probably methods/functions which will find you the date of
"last tuesday" and that sort of relative-to-absolute time addressing.

If I recall correctly, you seem to do a lot in PHP. In that case, take
a look at mktime(). http://php.net/mktime



-- 
mattwarden
mattwarden.com


This email proudly and graciously contributes to entropy.