CODEWITHSUNDEEP
mysqltypes
Martin
Martin

Reputation: 3

Datatype for storing continous time counter?

I need to store a time counter of how long a person has spent on mysite So if i logon today for 20 minutes and tomorrow for 1 hr then the counter should be 1 hour, 20 minutes. So the ideal format should be: YY:DD:HH:MM (Years:Days:Hours:Minutes). How to store this? I cant see this being calculated at runtime because after a lik 50 logins the system will need to always re-calculate the time from 0 up. So prefer to have this calculated after each logout and written to the user_table but I cant figure out a good data type except varchar? DB is mysql.

Upvotes: 0

Views: 363

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

Since it does not appear that MySQL 5.5 supports an SQL standard INTERVAL type, I suggest using a numeric type that stores the number of minutes in aggregate. When you need to present the value, call a function to convert the number of minutes into an interval in days, hours and minutes as a string.

Upvotes: 0

Gonzalo Larralde
Gonzalo Larralde

Reputation: 3541

You can use seconds, that'll be traduced in the integer type that you consider appropiate.

Then, you can transform this value to a date format.

That's what the engine does internally actually, AFAIK.

Good luck!

Upvotes: 1

Related Questions