What data type do you use for lengths of time?

[Topazan]

For instance, if you want to record that a certain effect lasts 2 days and 10 minutes, how do you represent that in the database?

The data types related to date or time seem mostly designed for recording a specific point on the timeline rather than measurements of length of time.  I'm thinking of just recording seconds as an Int, but I'd like to know if that's considered bad practice.

[lolninja]

Personally I would record an effects end time, so if I wanted something to last 2 days 10 minutes, I'd do something like...

INSERT INTO effects (type, ends) VALUES ('winning', (NOW() + 173400))

Then to tidy up the database you can just select anything with an ends < NOW() and jobs a good'un.

[Topazan]

Of course, but the question is how do you store the length of time between now and the end time?

Endtime = now() + X;

How do you store X?

Say you have a table of spells, each with a different duration, or a table of units each with a different training time.

[Nox]

A number of seconds...right? Should be usable with both timestamp and datetime, should be surely parsable to DateInterval object etc. the most universal one I can think of

[andrewjbaker]

In a nutshell, don't store calculable values in your DB.

Use timestamps and perform the calculation once you've retrieved (or are retrieving) them from the DB. Timestamps, at least UNIX-based ones, have an epoch of 00:00:00 UTC on 1 January 1970.

EDIT: /s/lease/least

[Nox]

@andrewjbaker this isn't calculable, Topazan wants e.g. to store duration of spell, that is a value in the spell's template, using which you'll calculate the end datetime when the spell is cast ... this is a case when duration is really needed

[Topazan]

Thanks for the help.  Nox has the right idea.

So would storing the number of seconds or milliseconds as an Int be an acceptable solution?  Or is there a better way?

[Nox]

Timestamp's (unix timestamp) smallest value is a second, so number of seconds ... I would guess it could be used in conjunction with datetime columns as well so as I said very universal ... and performant and not really large

Beside it being possibly large integers - which I belive is only a psychological thing - I don't see any downsides ... do you dislike it for some reason or just asking for other possibilities?

edit: btw I don't say there aren't many, you can wait if someone else posts their opinion

[Topazan]

Not really.  I was just wondering if there was some convention I was breaking that would complicate things down the line, or if there was was some built in method that worked better with SQL Date values.  Thanks for the help.

[andrewjbaker]

Sorry, misread. You're quite right NoxArt. Store the duration as number of seconds as an integer. My comment about not storing calculable values still holds tho'. ;-)

[Chris]

I'm thinking of just recording seconds as an Int, but I'd like to know if that's considered bad practice.

Yes, that's a bad practice, still everyone do it this way

It all boils down to the question if you care about Y2038 bug or not (http://en.wikipedia.org/wiki/Year_2038_problem).

Using DATETIME instead of int *might* be more Y2038 safe since *maybe* they could change this data type size in upcoming SQL versions to accomodate 64bit time. Or maybe they can't, I don't know
Using BigInt instead of int would basicly solve Y2038 problem or make it at least easy to fix with replacement of time() and other UNIX type time functions in your code/DB. But of course you pay with performance hit now for making less problems more than 20 years from now, so it is not exactly the best deal...

Personally, I use $gametime variable which is like UNIX timestamp but the start of the epoch is the launch of the game, so I bought myself much more time Sometimes I also use $gameday (day of the game round), more handy in some cases.


Anyway, this is purely paranoic point of view, those who are sane just use plain int and ignore the Y2038 bug

[Topazan]

Well, since we're talking about durations, I'll make sure that nothing in the game takes longer than 60 years to finish.

[Mutant]

Yes, that's a bad practice, still everyone do it this way

It all boils down to the question if you care about Y2038 bug or not (http://en.wikipedia.org/wiki/Year_2038_problem).

A lot of systems (particularly servers) are 64bit these days, so this is not such an issue.

[Nox]

Yea I would have that note against timestamp too...

Hmm I wonder about games in medieval/old-themed games (since unix is 1970+ and datetime 1000+), it's just they subtract X years from the date? Sounds a bit obscure (the value in DB isn't actually what it is really in the game) but on the other hand quite simple solution